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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
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 number of days from the base date (January 01 1900) we can easily add/subtract (n) number of days by just adding integer values.
|
1 2 3 |
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 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..
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE OR ALTER PROCEDURE dbo.uspGetDate @myDate DATETIME AS BEGIN SET XACT_ABORT ON; SET NOCOUNT ON; SELECT @myDate; RETURN; END |
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.
- 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
1234DECLARE @DTimeTest DATETIME;SET @DTimeTest = '20190709'--no time partSELECT @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 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
1234DECLARE @DTimeTest DATETIME;SET @DTimeTest = '12:23:34.456' -- no date partSELECT @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 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.
1234567--Default Sql Server languageSELECT * FROM sys.syslanguages WHERE [alias]='English'--read the current session language settingsDBCC 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 6th of December. We can change the mdy sequence in six different ways(six variations of the three letters 🙂 ) using the DATEFORMAT set option.
123SET DATEFORMAT DMY;GOEXECUTE dbo.uspGetDate @myDate = '12/06/50'
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.
12345EXECUTE 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 default value of 2049. The setting is available on the Server level(Advanced settings) and on database level for contained databases only.
12345678910--minimum 1753, max 9999EXECUTE sys.sp_configure@configname = 'two digit year cutoff',@configvalue = 2049;RECONFIGURE;--only contained databasesALTER DATABASE MyContainedDBSET TWO_DIGIT_YEAR_CUTOFF =2050;GOThe 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.
|
1 2 3 4 5 |
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
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.
|
1 2 3 |
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.
Script below shows the rounding in a visual manner.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
;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 |
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.
|
1 2 3 4 5 6 |
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 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 completely 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.
|
1 2 3 4 5 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
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 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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) |
|
1 2 3 4 5 6 7 8 9 10 |
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 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.
|
1 2 |
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)
|
1 2 |
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
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
Fgure 2, Nullable Columns
FIgure 5, timestamp vs rowversion