XML Feeds
Internally dates are stored as 2 integers. The first integer is the number of dates before or after the base date (1900/01/01). The second integer stores the number of clock ticks after midnight, each tick is 1/300 of a second.
So if we run the following code for the base date (1900/01/01)
- DECLARE @d DATETIME
- SELECT @d = '1900-01-01 00:00:00.000'
- SELECT CONVERT(INT,SUBSTRING(CONVERT(VARBINARY(8),@d),1,4)) AS DateInt,
- SUBSTRING(CONVERT(VARBINARY(8),@d),1,4) AS DateBinary
- SELECT CONVERT(INT,SUBSTRING(CONVERT(VARBINARY(8),@d),5,4)) AS TimeInt, SUBSTRING(CONVERT(VARBINARY(8),@d),5,4) AS TimeBinary
- Go
The results are
DateInt DateBinary
----------- ----------
0 0x00000000
TimeInt TimeBinary
----------- ----------
0 0x00000000
If we use the max date 9999/12/31
- DECLARE @d DATETIME
- SELECT @d = '9999-12-31 23:59:59.997'
- SELECT CONVERT(INT,SUBSTRING(CONVERT(VARBINARY(8),@d),1,4)) AS DateInt,
- SUBSTRING(CONVERT(VARBINARY(8),@d),1,4) AS DateBinary
- SELECT CONVERT(INT,SUBSTRING(CONVERT(VARBINARY(8),@d),5,4)) AS TimeInt, SUBSTRING(CONVERT(VARBINARY(8),@d),5,4) AS TimeBinary
- Go
we get the following result
DateInt DateBinary
----------- ----------
2958463 0x002D247F
TimeInt TimeBinary
----------- ----------
25919999 0x018B81FF
If you take binary values and convert to datetime you get the following results
- SELECT CONVERT(DATETIME,0x0000000000000001) --1 Tick 1/300 of a second
------------------------------------------------------
--1900-01-01 00:00:00.003
- SELECT CONVERT(DATETIME,0x000000000000012C) -- 1 minute = 300 ticks
------------------------------------------------------
--1900-01-01 00:00:01.000
- SELECT CONVERT(INT,0x12C) --= 300
- SELECT CONVERT(VARBINARY(3),300) --= 0x00012C
- SELECT CONVERT(DATETIME,0x0000000100000000) --add 1 day
------------------------------------------------------
--1900-01-02 00:00:00.000
For smalldatetime the time is stored as the number of minutes after midnight
Now here is some fun stuff
- DECLARE @d DATETIME
- SELECT @d = .0
- SELECT @d
- GO
------------------------------------------------------
--1900-01-01 00:00:00.000
- DECLARE @d DATETIME
- SELECT @d = .1
- SELECT @d
- GO
------------------------------------------------------
--1900-01-01 02:24:00.000
- DECLARE @d DATETIME
- SELECT @d = .12
- SELECT @d
- GO
------------------------------------------------------
--1900-01-01 02:52:48.000
- DECLARE @d DATETIME
- SELECT @d = '0'
- SELECT @d
- GO
Server: Msg 241, Level 16, State 1, Line 2
Syntax error converting datetime from character string.
- DECLARE @d DATETIME
- SELECT @d = 0
- SELECT @d
- GO
------------------------------------------------------
--1900-01-01 00:00:00.000
So there is no implicit conversion, o is fine 'o' is not
- DECLARE @d DATETIME
- SELECT @d = 20061030
- SELECT @d
- GO
Server: Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type datetime.
- DECLARE @d DATETIME
- SELECT @d = '20061030'
- SELECT @d
- GO
------------------------------------------------------
--2006-10-30 00:00:00.000
Here we have the reverse, the varchar value is fine but the int is not.
This happens because the max integer value that a datetime can take is 36523
If we run the following we are okay
- DECLARE @d DATETIME
- SELECT @d = 2958463
- SELECT @d
- GO
------------------------------------------------------
--9999-12-31 00:00:00.000
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.