This post continues our look at date and time data types in SQL Server.
The first one we look at this week is
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
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
'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.