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)

 T-SQL ```1 2 3 4 5 6 7 8 ``` ```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 ```
```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

 T-SQL ```1 2 3 4 5 6 7 8 ``` ```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 ```
```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

 T-SQL ```1 ``` `SELECT CONVERT(DATETIME,0x0000000000000001) --1 Tick 1/300 of a second `
`SELECT CONVERT(DATETIME,0x0000000000000001) --1 Tick 1/300 of a second `

——————————————————
–1900-01-01 00:00:00.003

 T-SQL ```1 ``` `SELECT CONVERT(DATETIME,0x000000000000012C) -- 1 minute = 300 ticks `
`SELECT CONVERT(DATETIME,0x000000000000012C) -- 1 minute = 300 ticks `

——————————————————
–1900-01-01 00:00:01.000

 T-SQL ```1 2 3 4 ``` ```SELECT CONVERT(INT,0x12C) --= 300 SELECT CONVERT(VARBINARY(3),300) --= 0x00012C   SELECT CONVERT(DATETIME,0x0000000100000000) --add 1 day ```
```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

 T-SQL ```1 2 3 4 ``` ```DECLARE @d DATETIME SELECT @d = .0 SELECT @d GO ```
```DECLARE @d DATETIME
SELECT @d = .0
SELECT @d
GO ```

——————————————————
–1900-01-01 00:00:00.000

 T-SQL ```1 2 3 4 ``` ```DECLARE @d DATETIME SELECT @d = .1 SELECT @d GO ```
```DECLARE @d DATETIME
SELECT @d = .1
SELECT @d
GO ```

——————————————————
–1900-01-01 02:24:00.000

 T-SQL ```1 2 3 4 ``` ```DECLARE @d DATETIME SELECT @d = .12 SELECT @d GO ```
```DECLARE @d DATETIME
SELECT @d = .12
SELECT @d
GO ```

——————————————————
–1900-01-01 02:52:48.000

 T-SQL ```1 2 3 4 ``` ```DECLARE @d DATETIME SELECT @d = '0' SELECT @d GO ```
```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.

 T-SQL ```1 2 3 4 ``` ```DECLARE @d DATETIME SELECT @d = 0 SELECT @d GO ```
```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

 T-SQL ```1 2 3 4 ``` ```DECLARE @d DATETIME SELECT @d = 20061030 SELECT @d GO ```
```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.

 T-SQL ```1 2 3 4 ``` ```DECLARE @d DATETIME SELECT @d = '20061030' SELECT @d GO ```
```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

 T-SQL ```1 2 3 4 ``` ```DECLARE @d DATETIME SELECT @d = 2958463 SELECT @d GO ```
```DECLARE @d DATETIME
SELECT @d = 2958463
SELECT @d
GO ```

——————————————————
–9999-12-31 00:00:00.000