However, I like to play with DBCC PAGE and look inside the stored information to learn how SQL Server actually works.
SQL Server stores dates always In a binary format, independently of the output format that the user might choose. This binary format is split into two parts. One part represents the date (number of days passed from the dates’ origin) and the other part represents the time (number of time unit passed since midnight).
If you had a smalldatetime value, date will be stored as the number of days from January 1st 1900, and for datetime values this number would be negative for dates before January 1st 1900.
For smalldatetime values, time is stored as the number of minutes since midnight, but for a datetime value, this number represents the number of ticks (1/300 sec) from midnight.
To see how SQL Server stores these values, run the following script:
USE Tempdb
GO
CREATE TABLE MyDate (thisDate datetime,
thisSmallDate smalldatetime)
INSERT Mydate
SELECT Getdate(), GetDate()
INSERT MyDate
SELECT '1/1/1800', GetDate()
INSERT MyDate
SELECT '1/1/1800 10:15', GetDate()
GO
SELECT *
FROM sysindexes
WHERE id = OBJECT_ID('MyDate')
-- In my case, I can see that the column First is 0x4B0000000100,
-- therefore the page where this data is stored is 0x4B, or 75
DBCC TRACEON(3604)
DBCC PAGE (Tempdb, 1, 75, 3)
/* you could see three rows with the values you inserted. In my case I got this:
Slot 0 Offset 0x60
------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP
1A786060: 00100010 00610fa5 00009421 94210161 ......a.!...a.!.
1A786070: 000002 ...
Fecha = Oct 29 2003 5:53AM
Fechita = Oct 29 2003 5:53AM
Slot 1 Offset 0x73
------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP
1A786073: 00100010 00000000 ffff7154 94210167 ........Tq..g.!.
1A786083: 000002 ...
Fecha = Jan 1 1800 12:00AM
Fechita = Oct 29 2003 5:59AM
Slot 2 Offset 0x86
------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP
1A786086: 00100010 00a8ea30 ffff7154 9421016e ....0...Tq..n.!.
1A786096: 000002 ...
Fecha = Jan 1 1800 10:15AM
Fechita = Oct 29 2003 6:06AM
Playing a little bit with hexadecimal values,
0x9421 = 37921 days
*/
SELECT DATEADD(day, 37921, '1/1/1900')
-- will give us 2003-10-29
-- 0xffff7154 = 4294930772 = -36524 (taking out 256*256*256*256)
SELECT DATEADD(day, -36524, '1/1/1900')
-- will give us 1800-01-01
-- 0x0161 = 353 minutes = 5:53 (the time I ran this script)
-- 0x00a8ea30 = 11070000 ticks = 36900 seconds = 615 minutes = 10:15
