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:

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. Datetime2 was first introduced in SQL Server 2008. However I think that some developers simply don't know about the advantages and disadvantages of datetime2.

Datetime

Let us first discuss datetime a little bit. As you can see above, it needs 8 bytes of storage and has a range from 1753-01-01 to 9999-12-31. Notably, it has a short range backwards. This is because Great Britain moved from the Julian to Gregorian calendar in 1752 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 the datetime datatype is the accuracy of 0.00333 seconds that is in fact 1/300 of a second.

This 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 into 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 that datetime stores the time in ticks. So 10800000 ticks since midnight means 10800000 times 1/300 of a second. Let's 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. Combined with the date we have 2015-11-29 10:00:00.

Remember that 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, which translates as 32-bit 2-complement to -3320. And 3320 substracted from 1900-01-01 is exactly 1890-11-29.

datetime2

All date and time datatypes introduced with SQL Server 2008 have a completely new storage type that we will examine now. 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 is 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 the 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.

datetime vs datetime2

Finally a simple and plain comparison between those two datatypes.

So overall you see datetime uses potentially more storage, has a lower and odd precision, has lower range and is not compatible with the SQL Standard, which makes your code behave differently on different DBMS. So if your application supports date, datetime2 and datetimeoffset I heavily advise on using the new datetime datatypes since they have barely any disadvantage.

Thanks for your time. For more interesting articles visit http://www.dirtyread.de/