There are many time and date formats in sql server with different ranges, accuracies, storage sizes and user defined fractional second precisions.
Below is a short overview.
|100 nanoseconds||3 to 5||Yes|
|100 nanoseconds||6 to 8||Yes|
|100 nanoseconds||8 to 10
We want to focus on the comparison of datetime and datetime2 format. In my current company i encounter a lot of legacy tables that use datetime. For sure that is because datetime2 was introduced first in SQL Server 2008. However i think that some developers simply don't know about the advantages and disadvantages of datetime2.
Let us first discuss datetime a little bit. As you can see above it needs 8 bytes of storage and has range from 1753-01-01 to 9999-12-31. Noticeable is the short range backwards. This is because Great Britain moved in 1752 from Julian to Gregorian calendar by skipping a few days. To be more precise the 2nd of September 1752 was followed by 14th of September 1752. Because a date before 1753 would be ambiguous the datetime type is not valid before 1753. Another quite noticeable property of datetime datatype is the accuracy of 0.00333 seconds that is in fact 1/300 of a second.
So that seems a little bit strange. We don't have millisecond accuracy with datetime. Ok but why? Let us analyze the datetime datatype in depth.
In a datetime we use 4 bytes for date and 4 bytes for time. How does that work exactly? Let's have a look.
DECLARE @test DATETIME = '2015-11-29 10:00:00.000'; SELECT CAST(@test as varbinary(8)) > 0x0000A55F00A4CB80
So 0x0000A55F00A4CB80 is hexadecimal. Let us separate the 8 bytes in two pieces. First the date.
0x0000A55F represents the date. In decimal it is 42335. That is the amount of days passed since 1900-01-01. Proof:
SELECT DATEADD(DD,42335,'1900-01-01') > 2015-11-29
Now for the time we have the last 4 bytes 0xA4CB80 translated to decimal it is 10800000. That means 10800000 ticks from midnight on. Remember i said the accuracy is 1/300 of a second? That is due to the fact datetime stores the time in ticks. So 10800000 ticks since midnight means 10800000 times 1/300 of a second. Lets calculate a little bit.
SELECT 10800000/ (300) as SecondsSinceMidnight, 10800000/ ( 300 * 60) as MinutesSinceMidnight, 10800000/ ( 300 * 60 * 60) as HoursSinceMidnight
So we have exactly 10 hours from midnight and that translates perfectly to 10:00:00 and combined with date we have 2015-11-29 10:00:00.
Remember datetime uses always 8 bytes of storage and also keep in mind that the first four bytes representing the date can be negative (2complement) since the date can be before 1900.
For instance in 1890-11-29 you get the first 4 bytes as 0xFFFFF308 with translates as 32-bit 2-complement to -3320. And 3320 substracted from 1900-01-01 is exactly 1890-11-29.
All date and time datatypes introduced with SQL Server 2008 have a completely new storage type that we examine now. Also the datetime2 datatype uses 6 to 8 bytes depending on the milisecond precision.
DECLARE @test DATETIME2(3) = '2015-11-29 10:00:00.000'; SELECT CAST(@test as varbinary(8)) > 0x0300512502BA3A0B
This time it gets a little bit more complicated. In all new datetime datatypes the LAST three bytes represent the date. That is due to a change of byte order.
So datetime is stored as little endian meaning the most significant byte is on the leftmost while in big endian the most significant byte ist stored on the rightmost position.
That means when we take 0x0300512502BA3A0B the date is not 0xBA3A0B but 0x0B3ABA since one byte is 2 hexadecimal digits.
Again with the math: 0x0B3ABA represents in decimal 735930. This is exactly the date we wanted:
SELECT DATEADD(DD,735930,CAST('0001-01-01' as date)) > 2015-11-29
Now that the bytes are converted we can just take the last bytes of little endian representation that is 0x0225510003. Keep in mind that the very last byte in little endian (that is the first byte in original big endian) is the precision stated. As you can see we defined datetime2(3) that means our very last byte is 0x03.
Doing the math: 0x02255100 is in decimal 36000000. Since we used precision 3 which means 3 digit precision we calculate the seconds first by dividing our number with 10 to the power of precision that is in our case 10³.
SELECT CAST(0x02255100 as INT) / POWER(10,3) as SecondsSinceMidnight, CAST(0x02255100 as INT) / ( POWER(10,3) * 60) as MinutesSinceMidnight, CAST(0x02255100 as INT) / ( POWER(10,3) * 60 * 60) as HoursSinceMidnight
This also translates perfectly to 10 hours 0 minutes 0 seconds just as stated.
Finally a simple and plain comparison between those two datatypes.
|max precision||odd precision of 1/300||100 nanosecond precision|
|user defined precision||no||yes ranging from 0 to 7|
|storage space||always 8 bytes||6 - 8 bytes depending on precision|
|useable with + or - operator||yes||no, use datediff, dateadd etc.|
|SQL Standard compatible||no||yes|
So overall you see datetime uses potentially more storage, has lower and odd precision, has lower range and is not compatible with SQL Standard which makes your code behaving differntly on different DBMS. So if your application supports date, datetime2 and datetimeoffset i heavily advice on using the new datetime datatypes since they have barely any disadvantage.
Thanks for your time.