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.
This week, we look at the DATETIME2 data type. I’m not the first person to think that this was probably not the best name for a data type, but here we are, a decade later.
DATETIME2 is, at its heart, a combination of the DATE and TIME data types we covered in previous weeks. DATE is 3 bytes long and TIME is between 3 and 5 bytes long depending on accuracy. This of course means that DATETIME2 can be anything from 6 to 8 bytes in length.
A DATETIME2 is formatted as 'YYYY-MM-DD HH:mm:ss.nnnnnnn', where YYYY is the year, MM is the month, DD is the day, HH is the hour (valid values 0 to 23), mm is the minutes (valid values 0 to 59), ss is the seconds (valid values 0 to 59), and n is zero or more decimal places representing a fraction of a second.
Because SQL Server is compiled for Intel CPUs, and x86 architecture processes its data byte-reversed (known as little-endian), the way DATETIME2 looks in memory and on the storage layer is that the time is stored first and then the date. We covered this in some detail last year.
DATETIME2 works in much the same way as the TIME data type. If you have not yet read last week’s post, I encourage you to do so. I’ll wait.
Welcome back. DATETIME2 uses the same scale that TIME uses, ranging from 0 to 7 decimal places for seconds. Remember, we’re telling the storage engine that we only need a certain accuracy, or granularity, for the time portion of the DATETIME2 value. The storage engine can then store these values more efficiently, depending on the scale.
Scale of a DATETIME2 value
When declaring a DATETIME2 data type in SQL Server or Azure SQL Database, we can define the scale as follows:
| Scale | Accuracy (in seconds) | Bytes used |
DATETIME2 (no scale) |
0.0000001 seconds | 8 bytes |
DATETIME2(0) |
1 second | 6 bytes |
DATETIME2(1) |
0.1 seconds | 6 bytes |
DATETIME2(2) |
0.01 seconds | 6 bytes |
DATETIME2(3) |
0.001 seconds | 7 bytes |
DATETIME2(4) |
0.0001 seconds | 7 bytes |
DATETIME2(5) |
0.00001 seconds | 8 bytes |
DATETIME2(6) |
0.000001 seconds | 8 bytes |
DATETIME2(7) |
0.0000001 seconds | 8 bytes |
Note that if we leave out the scale, the default uses a scale of 7, with full 100-nanosecond accuracy.
If we want to replace the old DATETIME data type with its unhelpful 3-millisecond accuracy, we can instead use a DATETIME2(3) data type, with 1-millisecond accuracy and save ourselves 1 byte per column. In a table with millions of rows, that is a significant saving.
Join me next week, when we take a first look at time zones in SQL Server and Azure SQL Database. It’s about to get complicated.
Share your DATETIME2 use-cases with me on Twitter, at @bornsql.
Photo by Lewis Ngugi on Unsplash.