Tag Archives: date

Its time for Datetime

Its time for Datetime


Summary

This post will try to explain the intricacies behind Sql Server’s Datetime/Datetime2 data type. The document will show the difference between the storage protocol used to store the data type and the way Sql Server interprets strings before storing it in datetime. It also mentions the visual representation of the data type by the client applications.
The differentiation between these three aspects can help us better understand and use datetime data types  in every day’s work.

DATETIME data type

Datetime data type has been around since early days of Sql Server. It is widely used across old and new applications. In Sql Server 2008 Microsoft introduced a new set of superior data types that handles dates and time: Datetime2, Date, Time and Datetimeoffset. Despite Microsoft’s recommendations to use the new datatypes, it seems to me that there are still a lots of post Sql Server 2008 applicaitons that still use the old datetime.

The storage protocol

Datetime data type uses 8bytes to store date and time parts of a datetime information. The 8bytes is used to store two 4byte integers;

  • First 4bytes stores the DATE part. The information is stored as an integer value that represents number of days before(-) or after(+) January 01, 1900.
  • last 4bytes (from left to right) stores the TIME part. The information is stored as an integer value that represents a number of 1/300 of a second from midnight (00:00:00.000). This is always positive integer value.

Tips and Ticks

In computer hardware everything ticks. The CPU has its own rhythm set by the CPU cycles and other hardware components have its own pase. The internal clock in computers keeps accurate time and it’s based on specific hardware components. This article may be a good starting point for those who wants to know more about the timekeeping in Sql Server and in PCs in general.

The precision of datetime’s TIME part is represented as 300 ticks in 1 second aka 3 decimal seconds. The precision is not down to 1ms(millisecond) since there is only 300 units in a second not 1000. To represent the precision in milliseconds we can say that

1tick = 1s /300,
1tick = 1000ms/300  =3.3ms.

So, in case of Sql Server’s datetime a tick is one 300th of a second or 3.3miliseconds.

Examples

The example below extracts date and time 4 byte integers from datetype and proves the above analysis.


Figure 1, datetime storage protocol

Add a day trick.

Knowing that the “first” 4bytes represents number of days from the base date (January 01 1900) we can easily add/subtract (n) number of days by just adding integer values.

The date and time presentation

The way applications present dates may be quite different i.e June 26  2019, 26.06.2019, 06-26-19 etc. However, this should be decoupled from the way Sql Server stores the data type. For datetime it is always 8byte (2x4byte) integer. As shown before, there is no ambiguity in how storage engine keeps the date and time. However, very often Client applications pass strings to databases, and the strings represent date and time in a certain format. Sql Server has to convert these strings to be able to store the information into the datetime data type.

Insert strings as date and time

Take for example application that sends date as ’12/06/50′. This may be generated by a date-time picker control and send to Sql Server as a string.
Now, Sql server needs to figure out how to interpret the date before storing it into datetime column, variable or parameter. The string value looks ambiguous since it can represent a few different dates. 12th of June 1950,  December the 6th 2050, 12th of June 3050 etc..

Lets call the stored proc passing the date parameter. EXEC dbo.uspGetDate @myDate = '12/06/50'
My SSMS returns 1950-12-06 00:00:00.000.  Again, I am not sure if 12 represents December or 12th day of June? Why 50 happened to be 1950 and not , say 2050? Why the time part shows midnight. The next section will try to answer these questions.

  1. Midnight. If we skip the time part of the datetime couple, the second 4byte integer will get the value of 0.  00:00:00.000
  2. if we insert only the time part of datetime couple, the first 4byte integer will get the value of 0, representing the starting point for counting the number of days from. January 01 1900
  3. What’s a month and what’s a day.  The way Sql Server understands string value when converting it to datetime is to use a pre-defined order of date elements; dmy, mdy ..etc. This sequence (dateformat) tells Sql Server the position of the elements of the string that represents month, day and year. Dateformat depends on the default language used on the connection level. The language defines the sequence of date elements. On the instance(server) level, the default language is set to us_english.

    From the queries above, we can learn that the dateformat Sql server expects when trying to convert ’12/06/50′ into a datetime is mdy (month-date-year). Following this rule Sql Server converts  ’12/06′ as  6th of December. We can change the mdy sequence in six different ways(six variations of the three letters 🙂 ) using the DATEFORMAT set option.

    Now, SSMS returns 1950-16-12 00:00:00.000.  12th of June. We can also use names of the months – see BOL for more examples.
  4. Why 50 is 1950? There is a setting called “Two digit Year Cutoff” and it has default value of 2049. The setting is available on the Server level(Advanced settings) and on database level for contained databases only.

    The setting defines how Sql Server interprets the two digit year.
    – A two-digit year that is less than or equal to the last two digits of the cutoff year will have the same first two digits as the cutoff year.
    – A two-digit year that is greater than the last two digits of the cutoff year, SQL Server interprets the first two digits as a number that is one less than the cutoff year’s first two digits.

    In our case the two digit year is 50. 50 is greater than 2049. Following the rule Sql Server subtracts 1 from 20 (20 -1 = 19)to get the first two digits for the year. That’s why our date has year 1950.

ISO Standard

As mentioned above, converting strings to datetime data type can be ambiguous and may lead to logical errors. To avoid confusion, we can use string format that follows ISO Standard. The format ALWAYS represents date and time as YYYYMMDD hh:mm:ss.nnn. regardless of DATEFORMAT and/or LANGUAGE settings.

Round my Ticks

Yet another interesting thing about datetime’s is its 1/300 of a sec precision. The rounding happens on the  00:00:00.00x decimal place. The third decimal place is ALWAYS  0, 3 or 7 :).
So, lets test this behavior.

The stored datetime value is rounded to 1971-06-26 07:30:55.997.

Script below shows the rounding in a visual manner.


Figure 2, datetime rounding

Minimum date quirk

And yes, there is another unusual thing about datetime data type. We learned that the starting point for storing DATE part of datetime is 01 January 1900, or 19000101 in the ISO standard format. So, if we want to store a date before the starting point, say 01 January 1753, storage engine protocol will store (-)No.Of days from 01 January 1900.

The output shows -53690 days from the start date. The number matches the DATEDIFF output. Now, if we try to store just a day later, ’31 December 1752′ The conversion will fail with
Msg 242, Level 16, State 3, Line 184
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Hmm, we are definitely not running out of 4byte integers, so why 1753?
The reason seems to be more of a historical nature. On 2nd of September 1752, Great Britain replaced Julian with the Gregorian calendar, which differed from the original by 12 days. So, the “next” day was 14th of Sept :). In order to avoid confusion when calculating the number of days between a date before 02.09.1752 and a date after, Sybase engineers decided not to allow dates before 1753. Simple as that 🙂

Datetime presentation

SQL Server can present information stored as datetime data type as a string in many different ways. The formatted strings that represent dates are usually sent to Client applications i.e for the reporting purposes. This should be totally decoupled from the storage protocol mentioned before. For the presentation purposes, SQL Server provides CONVERT function. The function’s third, optional parameter defines the output format of the string that represents the converted datetime. The script below shows the idea behind the formatted strings.

I found the following script very useful for a quick overview of the different formatting styles.

The full list of the formatting style codes can be found here.

DATETIME2 data type

It would be impossible to conclude this blog without mentioning datetime’s younger brother, DATETIME2.
The new data type was introduced in SQL Server 2008 as a part of a new set of date/time data types and functions.  After the release, Microsoft strongly recommended new date and time data types:
Use the timedatedatetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. timedatetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.”

My personal opinion is that programmers, now over a decade since datetime2 was fist introduced,  still prefer datetime. Not sure why, but this is something I see in the industry regardless of the size and/or complexity of the Sql applications.

The main functional advantages of datetime2 over datetime are:

  • higher precision. 100 nanoseconds.  1ns = 10E-9s, 100ns = 10E-7s
  • larger date range:  from 01 January 0001 00:00:00.0000000 – 31 December 9999 23:59:59.9999999
  • Is an ANSI Compliant
  • Optional user-specified precision (0-7) i.e DATETIME(3) , precision 1/1000 of a second.

Datetime2 ticks better

The precision of DATETIME2 is 100ns (nano seconds), or 10E-7s (1/10,000,000), hence 7 decimal places in the TIME part of the data type.
The following test shows storage protocol used for datetime2 data type.


Figure 3, datetime2 storage protocol

There are a few interesting things in the way Sql Server storage engine deals with datetime2.

  • The maximum length of the datatype is 8bytes, SELECT dt2_maxSize = DATALENGTH(SYSDATETIME()). Size required to store datetime2 into a binary data type is 9bytes.  The one extra byte is used to store precision*.
  • Bytes are stored in reversed order – more details on the famous argument between Lilliputans can be found in an excellent post here 🙂
  • Date part is stored in the “last” 3bytes.
  • Storage size required to store Time part may vary between 3 – 5 bytes depending on requested precision.

*NOTE: DATETIME2(n) is a fixed data type. Depending on scale(n) Sql server storage engine use 6-8 bytes to physically store the information on data page. The one extra byte is not needed since the precision cannot vary across different rows.

Configurable precision

The younger brother gives us ability to control the date type’s precision. As mentioned above, the maximum precision is 100ns, or 7 decimal places in the time part. If not specified, DATETIME2 uses the maximum precision.
The query below shows different precision of the same datetime2 value.

Datetime(3) precision is “more precise” than datetime since it provides 1 milisecond (1/1000 of a second) precision rather then datetime’s 1/300 of a second precision.

Add a day trick does not work with DATETIME2.

Msg 206, Level 16, State 2, Line 106
Operand type clash: datetime2 is incompatible with int

Minimum date quirk or not?

Sybase engineers decided not to “mess” with the Julian to Gregorian calendar switch from 02.09.1752 (just for the record, 09 is September 🙂 ), so they limited datetime with a minimum date and set  the value to 01.01.1753.
Datetime2 has no similar limitation and allows dates starting from the “beginning of the time”, 01.01.0001. Now, we can ask a philosophical question: “How many days are between 01.09.1752  (midnight) and 03.09.1752 (midnight)

So, what is the best result: Conversion failure, 2 days or 14 days?

Conclusion

Datetime data type is widely used across many SQL Server applications. It has its quirks and features as well as its modern and more powerful version – datetime2. Both data types unambiguously store date and time. This should be decoupled from the way our program presents these values.  The presentation is always in a form of a formatted string i.e ’01 September 05′, ‘June, 01 1998’ etc. This is how SSMS or other Client applications present date and time. Passing string values that represents dates into datetime data types may be unclear since Sql Server needs to figure out what comes first; day, month or year. Different Client applications may use different components to retrieve date/time information passed through the UI. Those components may format dates in different way, so we need to be sure that our database understands the formats properly i.e 01.11.19 should be 11th of Jan 2019 not i.e 01st of November 1819 🙂

Thanks for reading.

Dean Mincic