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 1300 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