Dates and Times in SQL Server: DATE

This post continues our look at date and time data types in SQL Server.

SQL Server 2008 introduced new data types to handle dates and times in a more intelligent way than the DATETIME and SMALLDATETIME types that we looked at previously.

The first one we look at this week is DATE. Whereas DATETIME uses eight bytes and SMALLDATETIME uses four bytes  to store their values, DATE only needs a slender three bytes to store any date value between 0001-01-01 and 9999-12-31 inclusive.

As we discovered in a previous post (“So like, what is a byte?”) three bytes can store up to 16,777,216 possible values. This is because a single byte can store up to 256 values (2 to the power of 8). Multiplying this out gives us 256 to the power of 3 (or 2 to the power of 24), for more than 16 million values.

Given that there are 365.2425 days on average per year, a range of 10,000 years is only 3.6 million values, which fit comfortably in three bytes. (Perhaps in a later blog post, we can dig deeper and see how these are stored on the file system.)

DATE has an accuracy of 1 day (which is kind of obvious) and is based on the Gregorian calendar.

The default string format (called a string literal) for DATE is 'YYYY-MM-DD'. Note however that regional settings might affect your string literals, so be wary when passing date values around as strings. We will dive into date and time formats in later posts.

If you have any thoughts about the DATE format, find me on Twitter at @bornsql.

Photo by Curtis MacNewton on Unsplash.