business

Datetime

PHP is already trying to convert it to the default timezone. Get Database Weekly for a roundup of all the biggest SQL news from around the web. Want more great articles datetime this?

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. However I think that some developer simply don’t know about the advantages and disadvantages of datetime2. 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. That seems a little bit strange. We don’t have millisecond accuracy with datetime. Let us analyze the datetime datatype in depth. In a datetime we use 4 bytes for date and 4 bytes for time.

Let us separate the 8 bytes in two pieces. That is the amount of days passed since 1900-01-01. That means 10800000 ticks from midnight on. That is due to the fact datetime stores the time in ticks. 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. 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. Also the datetime2 datatype uses 6 to 8 bytes depending on the milisecond precision. In all new datetime datatypes the LAST three bytes represent the date. That is due to a change of byte order.