How Are Dates Stored Internally In SQL Server?

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