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