Page Contents

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 them 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 everyday work.

DATETIME data type

The DATETIME data type has been around since the 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 handle dates and time: Datetime2, Date, Time, and Datetimeoffset. Despite Microsoft’s recommendations to use the new datatypes, it seems to me that there is still a lot of post SQL Server 2008 applications 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;

The first 4bytes store the DATE part. The information is stored as an integer value that represents the number of days before(-) or after(+) January 01, 1900.
The last 4bytes (from left to right) store 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 a 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 their own pace. 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 want to know more about 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 are 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 the 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 the date type and proves the above analysis.

DECLARE @DTime DATETIME 
       ,@DTime_HEX BINARY(8)          --datetime binary
       ,@DTime_DATEpart_HEX BINARY(4) --date part binary
       ,@DTime_TIMEpart_HEX BINARY(4) --time part binary
       ,@DTime_DATEpart_INT INT       --date part integer (no of days before or after 1900-01-01)
       ,@DTime_TIMEpart_INT INT       --number of ticks since midnight 00:00:00.000	   
       ,@DTime_TIMEpart_DEC_ms DECIMAL(38,3); --number of miliseconds since midnight 1tick = 3.3333333333(3) ms

SET @DTime ='20190712 08:18:00.973';---GETDATE(); --get current datetime

--split the datetime 8bytes into 4b Date and 4b Time
SET  @DTime_HEX =  CAST(@DTime AS BINARY(8)); --hex datetime

SET  @DTime_DATEpart_HEX = SUBSTRING(@DTime_HEX,1,4); --first 4 bytes (hex date)
SET  @DTime_TIMEpart_HEX = SUBSTRING(@DTime_HEX,5,4); --last 4 bytes (hex time)

SET  @DTime_DATEpart_INT = CAST(@DTime_DATEpart_HEX AS INTEGER); --first 4 bytes (integer)
SET  @DTime_TIMEpart_INT = CAST(@DTime_TIMEpart_HEX AS INTEGER); --last 4 bytes (integer)

SET @DTime_TIMEpart_DEC_ms = @DTime_TIMEpart_INT * 3.3333333333;


--analyse DATE part
SELECT [Current datetime]     = @DTime
      ,[datetime Size(Bytes)] = DATALENGTH(@DTime)
      ,[datetime Binary(hex)] = @DTime_HEX

      --date
      ,[Date_part(hex)]      = @DTime_DATEpart_HEX
      ,[Date_part(Integer)]  = @DTime_DATEpart_INT --days from 1900-01-01
      ,[DaysFrom 1900.01.01(datediff)] = DATEDIFF(DAY,'19000101',@DTime) -- confirm the difference

      --time
      ,[Time_part(hex)]     = @DTime_TIMEpart_HEX
      ,[Time_part(Integer)]  = @DTime_TIMEpart_INT --ticks from midnight 
      ,[Time_part(ms)]       = @DTime_TIMEpart_DEC_ms --miliseconds from midnight
      ,[Hours]               = FLOOR( (@DTime_TIMEpart_DEC_ms/1000/60/60) %24) --hours from time part
      ,[Minutes]             = FLOOR( (@DTime_TIMEpart_DEC_ms/1000/60) %60 )   --minutes from time part
      ,[Seconds]             = FLOOR( (@DTime_TIMEpart_DEC_ms/1000) %60 )      --seconds from time part

      ,[1/300 of a second]   = CAST ( (@DTime_TIMEpart_DEC_ms / 1000) % 1 AS DECIMAL(3,3)) --datetime's precision 1/300 of a second

Figure 1, DATETIME storage protocol

Add a day trick.

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

SELECT CurrentDate = GETDATE()
       ,TheNextDay = GETDATE() + 2
       ,ThreeDaysAgo = GETDATE() - 3

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 an 8byte (2x4byte) integer. As shown before, there is no ambiguity in how the 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 an example application that sends the date as ’12/06/50′. This may be generated by a date-time picker control and sent to SQL Server as a string.
Now, SQL Server needs to figure out how to interpret the date before storing it in a 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.

CREATE OR ALTER PROCEDURE dbo.uspGetDate		
    @myDate DATETIME
AS
BEGIN
    SET XACT_ABORT ON;
    SET NOCOUNT ON;

    SELECT @myDate;

    RETURN;
END

Let’s 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 the 12th day of June. Why does 50 happen to be 1950 and not, say, 2050? Why the time part shows midnight? The next section will try to answer these questions.

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

DECLARE @DTimeTest DATETIME;

SET @DTimeTest = '20190709'--no time part
SELECT @DTimeTest , [Date]=SUBSTRING(CAST(@DTimeTest AS BINARY(8)),1,4),[Time]=SUBSTRING(CAST(@DTimeTest AS BINARY(8)),5,4)

if we insert only the time part of the 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

DECLARE @DTimeTest DATETIME;

SET @DTimeTest = '12:23:34.456' -- no date part
SELECT @DTimeTest , [Date]=SUBSTRING(CAST(@DTimeTest AS BINARY(8)),1,4),[Time]=SUBSTRING(CAST(@DTimeTest AS BINARY(8)),5,4)

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 the 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.
```
--Default Sql Server language
SELECT * FROM sys.syslanguages WHERE [alias]='English'

--read the current session language settings
DBCC USEROPTIONS;

--capture the Login event during the application login
```
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 the 6th of December. We can change the mdy sequence in six different ways(six variations of the three letters 🙂 ) using the DATEFORMAT set option.
```
SET DATEFORMAT DMY;
GO
EXECUTE dbo.uspGetDate @myDate = '12/06/50'
```
Now, SSMS returns 1950-16-12 00:00:00.000. 12th of June. We can also use the names of the months – see BOL for more examples.
```
EXECUTE dbo.uspGetDate @myDate = '12/06/50';
EXECUTE dbo.uspGetDate @myDate = '06 December 50';
EXECUTE dbo.uspGetDate @myDate = 'December 06 50';
EXECUTE dbo.uspGetDate @myDate = 'Dec 06 50';
... etc
```
Why 50 is 1950? There is a setting called “Two-digit Year Cutoff” and it has the default value of 2049. The setting is available on the Server level(Advanced settings) and on the database level for contained databases only.
```
--minimum 1753, max 9999
EXECUTE sys.sp_configure 
        @configname = 'two digit year cutoff' 
        ,@configvalue = 2049;
RECONFIGURE;

--only contained databases
ALTER DATABASE MyContainedDB
    SET TWO_DIGIT_YEAR_CUTOFF =2050;
GO
```
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 the 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 a string format that follows ISO Standards. The format ALWAYS represents date and time as YYYYMMDD hh:mm:ss.nnn. regardless of DATEFORMAT and/or LANGUAGE settings.

DECLARE @isoDTime DATETIME;
SET @isoDTime ='19501206 13:30:55.123';

SELECT @isoDTime;
--The date will always be the 6th of December 1950

Round my Ticks

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

DECLARE @iWantMyPrecisionBack DATETIME; 
SET @iWantMyPrecisionBack ='19710626 07:30:55.995';
SELECT @iWantMyPrecisionBack;

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

The script below shows the rounding in a visual manner.

;WITH getNums AS --generate 100 numbers
(
    SELECT TOP(100)
           rn = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
    FROM sys.columns, sys.columns c
    ORDER BY (SELECT NULL)
), getLZrs AS --generate the expected results (date and time represented as a string)
(
     SELECT TOP(100)
        rnZ = IIF(rn<10,'00',IIF(rn<100,'0',''))+ cast (rn AS VARCHAR(3))
     FROM getNums
     ORDER BY rnZ
)

SELECT  DateTime_Expected =  '20191206 13:30:55.' +rnZ
       ,DateTime_Rounded = CONVERT(DATETIME,'20191206 13:30:55.' +rnZ) --rounding    
FROM getLZrs

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

DECLARE @minDate DATETIME =  '17530101'; --'17521231'

SELECT Date = @minDate
      ,Date_hex = SUBSTRING(CAST(@minDate AS BINARY(8)),1,4) -- 4byte date part
      ,Date_int = CAST(SUBSTRING(CAST(@minDate AS BINARY(8)),1,4) AS INTEGER) --no of days integer
      ,Check_diff = DATEDIFF(DAY,'19000101','17530101') --just checking

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 the 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 the 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 reporting purposes. This should be completely decoupled from the storage protocol mentioned before. For presentation purposes, SQL Server provides the 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.

DECLARE @mydate DATETIME= '20190710 09:30:55.123' --iso standard used to insert date into a variable

SELECT  [SSMS - style=121] = @mydate
       ,[CONVERT date style=109] = CONVERT(VARCHAR(30),@mydate,109)
       ,[CONVERT date style=108] = CONVERT(VARCHAR(30),@mydate,108) --only time

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

DECLARE @dt DATETIME = GETDATE() 
;WITH getStyleNo AS
(
    SELECT TOP(200)
          StyleId     = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
        ,[DateFormat] = TRY_CONVERT(NVARCHAR(40),@dt,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))
    FROM sys.columns,sys.columns c
    ORDER BY (SELECT NULL)
)
SELECT *
FROM getStyleNo
WHERE getStyleNo.[DateFormat] IS NOT NULL

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 time, date, datetime2, and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2, 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 first 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 (nanoseconds), or 10E-7s (1/10,000,000), hence 7 decimal places in the TIME part of the data type.
The following test shows the storage protocol used for the datetime2 data type.

DECLARE @DTime DATETIME2
       ,@DTime_HEX BINARY(9)          --datetime binary
       ,@DTime_DATEpart_HEX BINARY(3) --date part binary
       ,@DTime_TIMEpart_HEX BINARY(5) --time part binary
       ,@DTime_DATEpart_HEX_rev BINARY(3) --reversed binary date part
       ,@DTime_TIMEpart_HEX_rev BINARY(5) --reversed binary time part
       
       ,@DTime_DATEpart_INT INT       --date part integer (no of days before or after 1900-01-01)
       ,@DTime_TIMEpart_INT BIGINT    --number of ticks since midnight 00:00:00.0000000	   
       ,@DTime_TIMEpart_DEC_ns BIGINT --number of miliseconds since midnight 1tick = 100ns
       ,@Precision BINARY(1);

    SET @DTime ='2019-07-16 11:02:06.7695434' --SYSDATETIME(); --get current datetime

    --split the datetime(8+1)byte into 1b precisoin, 3byte date and 5byte time parts
    SET  @DTime_HEX =  CAST(@DTime AS BINARY(9)); --hex datetime

    --precision (1st byte)
    SET  @Precision = SUBSTRING(@DTime_HEX,1,1); -- first byte is precision 0,1,2...7

    --time (bytes 2-6 reversed)
    SET @DTime_TIMEpart_HEX     = SUBSTRING(@DTime_HEX,2,6)
    SET @DTime_TIMEpart_HEX_rev = CAST(REVERSE(@DTime_TIMEpart_HEX) AS BINARY(5))
    SET @DTime_TIMEpart_INT     = CAST(@DTime_TIMEpart_HEX_rev AS BIGINT) --number of ticks

    --date (bytes 7-9 reversed)
    SET  @DTime_DATEpart_HEX     = SUBSTRING(@DTime_HEX,7,3)
    SET  @DTime_DATEpart_HEX_rev = CAST(REVERSE(@DTime_DATEpart_HEX) AS BINARY(3))
    SET  @DTime_DATEpart_INT     = CAST(@DTime_DATEpart_HEX_rev AS INTEGER)

    --convert ticks into nano-seconds
    SET @DTime_TIMEpart_DEC_ns = @DTime_TIMEpart_INT * 100  --@DTime_TIMEpart_INT  --No of ticks since midnight. 1tick =100ns

    --analyse DATE part
    SELECT [Current datetime]     = @DTime
          ,[datetime Size(Bytes)] = DATALENGTH(@DTime) -- +1b for the precision		
          ,[datetime Binary(hex)] = @DTime_HEX

          ,[Precision] = @Precision
      
          --date
          ,[Date_part(hex)]          = @DTime_DATEpart_HEX 
          ,[Date_part(hex) reversed] = @DTime_DATEpart_HEX_rev
          ,[Date_part(Integer)]      = @DTime_DATEpart_INT  --days from January 01 0001
          ,[DaysFrom 00001.01.01(datediff)] = DATEDIFF(DAY,'00010101',@DTime) -- confirm the difference

    SELECT 
          --time
           [Time_part(hex)]          = @DTime_TIMEpart_HEX
          ,[Time_part(hex)_reversed] = @DTime_TIMEpart_HEX_rev
          ,[Time_part(Integer/ticks)]= @DTime_TIMEpart_INT --ns from midnight
          ,[Time_part(ns)]           = @DTime_TIMEpart_DEC_ns --ns from midnight
          ,[Hours]                   = (@DTime_TIMEpart_DEC_ns /1000000000/60/60 ) % 24 --/1000000000 gets seconds from ns 1s = 10e-9ns
          ,[Minutes]                 = (@DTime_TIMEpart_DEC_ns/1000000000/60) %60       --minutes from time part
          ,[Seconds]                 = (@DTime_TIMEpart_DEC_ns/1000000000) %60          --seconds from time part
          ,[Precision(100ns)]        = CAST((@DTime_TIMEpart_DEC_ns/1000000000.0000000) % 1 AS DECIMAL(8,7)) --datetime's precision 100ns - 7 decimal places

GO

Figure 3, datetime2 storage protocol

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

The maximum length of the datatype is 8bytes, SELECT dt2_maxSize = DATALENGTH(SYSDATETIME()). The 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 Lilliputians can be found in an excellent post here 🙂
The date part is stored in the “last” 3bytes.
The storage size required to store the Time part may vary between 3 – 5 bytes depending on the requested precision.

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

Configurable precision

The younger brother gives us the 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.

DECLARE @tsql NVARCHAR(MAX);

SET @tsql = N'DECLARE @dtime2 DATETIME2 = SYSDATETIME(); ' + CHAR(13);

;WITH precisionNums AS 
(
    SELECT TOP(8) n= ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1
    FROM sys.columns c
)
SELECT TOP 100 PERCENT @tsql +=N'SELECT [Precision] = N''DATETIME2('+CAST(n AS NCHAR(1))+')'', [value] = CAST(@dtime2 AS DATETIME2('+CAST(n AS NCHAR(1))+N')), [DATALENGTH]=DATALENGTH(CAST(@dtime2 AS DATETIME2('+CAST(n AS NCHAR(1))+N')))'
                             +CHAR(13)+ IIF(n=0,N'',N'UNION ALL'+CHAR(13))
FROM precisionNums
ORDER BY n DESC

--PRINT @tsql
EXEC(@tsql)

Precision    value                       DATALENGTH
------------ --------------------------- -----------
DATETIME2(7) 2019-07-17 11:23:17.1738815 8
DATETIME2(6) 2019-07-17 11:23:17.1738820 8
DATETIME2(5) 2019-07-17 11:23:17.1738800 8
DATETIME2(4) 2019-07-17 11:23:17.1739000 7
DATETIME2(3) 2019-07-17 11:23:17.1740000 7
DATETIME2(2) 2019-07-17 11:23:17.1700000 6
DATETIME2(1) 2019-07-17 11:23:17.2000000 6
DATETIME2(0) 2019-07-17 11:23:17.0000000 6

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

Add a day trick does not work with DATETIME2.

DECLARE @dt2 DATETIME2 = SYSDATETIME();
SELECT CurrentDate = @dt2+ 1;

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)

SELECT DATEDIFF(DAY,CAST('17520901' AS DATETIME),CAST('17520903' AS DATETIME))
SELECT DATEDIFF(DAY,'17520901','17520903') --DATEDIFF implicitly casts string literals as a datetime2 type

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

Conclusion

The 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 represent 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 ways, so we need to be sure that our database understands the formats properly i.e 01.11.19 should be the 11th of Jan 2019 not i.e 01st of November 1819 🙂

Thanks for reading.

Dean Mincic

SQL CHIT CHAT … Blog about Sql Server

Tag Archives: time

Its time for Datetime

Its time for Datetime

Summary

DATETIME data type

The storage protocol

Tips and Ticks

Examples

The date and time presentation

Insert strings as date and time

ISO Standard

Round my Ticks

Minimum date quirk

Datetime presentation

DATETIME2 data type

Datetime2 ticks better

Configurable precision

Minimum date quirk or not?

Conclusion

In Codd We Trust .. It's Ted or bust