Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Datetime vs. Datetime2

By Robert Hartmann,

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.

Type Format Range Accuracy Storage
in bytes

User 
defined
precision

time hh:mm:ss [.nnnnnnn] 00:00:00.0000000
through
23:59:59.9999999
100 nanoseconds 3 to 5 Yes
date YYYY-MM-DD 0001-01-01 through
9999-12-31
1 day 3 No
smalldatetime YYYY-MM-DD
hh:mm:ss
1900-01-01 through
2079-06-06
1 minute 4 No
datetime YYYY-MM-DD
hh:mm:ss [.nnn]
1753-01-01 through
9999-12-31
0.00333 seconds 8 No
datetime2 YYYY-MM-DD
hh:mm:ss [.nnnnnnn]

0001-01-01
00:00:00.0000000
through 9999-12-31
23:59:59.9999999

100 nanoseconds 6 to 8 Yes
datetimeoffset YYYY-MM-DD 0001-01-01
00:00:00.0000000
through 9999-12-31
23:59:59.9999999
(int UTC)
100 nanoseconds 8 to 10
(2 for 
zone)
Yes


We want to focus on the comparison of datetime and datetime2 format. In my current company I encounter a lot of legacy tables that uses datetime. For sure that is because datetime2 was introduced first in SQL Server 2008. However I think that some developer 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 byte of storage and has a 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 ambigous, the datetime type is not valid before 1763. Another quite noticeable property of datetime datatype is the accuracy of 0.00333 seconds that is in fact 1/300 of a second.

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. 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 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.

datetime2

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.

datetime vs datetime2

Finally a simple and plain comparison between those two datatypes.

datetime datetime2
max precise 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 a lower and odd precision, has lower range and is not compatible with the SQL Standard, which makes your code behaving differently 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. For more interesting articles visit http://www.dirtyread.de/

 
Total article views: 12815 | Views in the last 30 days: 44
 
Related Articles
FORUM

Adding Minutes to DATETIME2

It compiles with DATETIME, but not DATETIME2

BLOG

On the Advantages of DateTime2(n) over DateTime

Starting with SQL 2008, we database developers started becoming more familiar with datetime2.  Somet...

SCRIPT

datetime precision

Accuracy and precision go hand-in-hand. This script helps trim date values to whatever size is store...

FORUM
FORUM

Datetime vs. Datetime2

Comments posted to this topic are about the item [B]Datetime vs. Datetime2[/B] Thank you Robert for ...

Tags
datetime    
datetime2    
 
Contribute