# LessThanDot

Data Management

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

## LTD Social Sitings

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

## XML Feeds

 « Getting The Percentage Of NULLS And Values In A SQL Server Table How Does Between Work With Dates In SQL Server? »

# How Are Dates Stored In SQL Server?

by SQLDenis on Aug 04, 2008 in categories Data Modelling & Design

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)

tsql
1. DECLARE @d DATETIME
2. SELECT @d = '1900-01-01 00:00:00.000'
3.
4.
5. SELECT CONVERT(INT,SUBSTRING(CONVERT(VARBINARY(8),@d),1,4)) AS DateInt,
6. SUBSTRING(CONVERT(VARBINARY(8),@d),1,4) AS DateBinary
7. SELECT CONVERT(INT,SUBSTRING(CONVERT(VARBINARY(8),@d),5,4)) AS TimeInt, SUBSTRING(CONVERT(VARBINARY(8),@d),5,4) AS TimeBinary
8. Go

The results are

DateInt DateBinary
----------- ----------
0 0x00000000

TimeInt TimeBinary
----------- ----------
0 0x00000000

If we use the max date 9999/12/31

tsql
1. DECLARE @d DATETIME
2. SELECT @d = '9999-12-31 23:59:59.997'
3.
4.
5. SELECT CONVERT(INT,SUBSTRING(CONVERT(VARBINARY(8),@d),1,4)) AS DateInt,
6. SUBSTRING(CONVERT(VARBINARY(8),@d),1,4) AS DateBinary
7. SELECT CONVERT(INT,SUBSTRING(CONVERT(VARBINARY(8),@d),5,4)) AS TimeInt, SUBSTRING(CONVERT(VARBINARY(8),@d),5,4) AS TimeBinary
8. 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

tsql
1. SELECT CONVERT(DATETIME,0x0000000000000001) --1 Tick 1/300 of a second

------------------------------------------------------
--1900-01-01 00:00:00.003

tsql
1. SELECT CONVERT(DATETIME,0x000000000000012C) -- 1 minute = 300 ticks

------------------------------------------------------
--1900-01-01 00:00:01.000

tsql
1. SELECT CONVERT(INT,0x12C) --= 300
2. SELECT CONVERT(VARBINARY(3),300) --= 0x00012C
3.
4. 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

tsql
1. DECLARE @d DATETIME
2. SELECT @d = .0
3. SELECT @d
4. GO

------------------------------------------------------
--1900-01-01 00:00:00.000

tsql
1. DECLARE @d DATETIME
2. SELECT @d = .1
3. SELECT @d
4. GO

------------------------------------------------------
--1900-01-01 02:24:00.000

tsql
1. DECLARE @d DATETIME
2. SELECT @d = .12
3. SELECT @d
4. GO

------------------------------------------------------
--1900-01-01 02:52:48.000

tsql
1. DECLARE @d DATETIME
2. SELECT @d = '0'
3. SELECT @d
4. GO

Server: Msg 241, Level 16, State 1, Line 2
Syntax error converting datetime from character string.

tsql
1. DECLARE @d DATETIME
2. SELECT @d = 0
3. SELECT @d
4. GO

------------------------------------------------------
--1900-01-01 00:00:00.000

So there is no implicit conversion, o is fine 'o' is not

tsql
1. DECLARE @d DATETIME
2. SELECT @d = 20061030
3. SELECT @d
4. GO

Server: Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type datetime.

tsql
1. DECLARE @d DATETIME
2. SELECT @d = '20061030'
3. SELECT @d
4. 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

tsql
1. DECLARE @d DATETIME
2. SELECT @d = 2958463
3. SELECT @d
4. GO

------------------------------------------------------
--9999-12-31 00:00:00.000

10630 views

Comment from: Rob Crawford [Visitor]
OK, so they're storing date times as "days since 1900-01-01" and "ticks since midnight" -- but 1900-01-01 WHERE, and midnight WHERE? Local? UTC? US Pacific time?

I fear the answer is "local".
11/21/12 @ 13:21
Comment from: SQLDenis [Member]
it stores what you pass in

if you call GETDATE() then it is whatever the function returns from the server depending on the clock. If you call GETUTCDATE() it will use GMT time

From Books On Line.

If you run the following functions at 2007-05-03 18:34:11.933

```SELECT 'SYSDATETIME()      ', SYSDATETIME();
SELECT 'SYSDATETIMEOFFSET()', SYSDATETIMEOFFSET();
SELECT 'SYSUTCDATETIME()   ', SYSUTCDATETIME();
SELECT 'CURRENT_TIMESTAMP  ', CURRENT_TIMESTAMP;
SELECT 'GETDATE()          ', GETDATE();
SELECT 'GETUTCDATE()       ', GETUTCDATE();

/* Returned:
SYSDATETIME()            2007-05-03 18:34:11.9351421
SYSDATETIMEOFFSET()      2007-05-03 18:34:11.9351421 -07:00
SYSUTCDATETIME()         2007-05-04 01:34:11.9351421
CURRENT_TIMESTAMP        2007-05-03 18:34:11.933
GETDATE()                2007-05-03 18:34:11.933
GETUTCDATE()             2007-05-04 01:34:11.933
*/```
11/21/12 @ 13:36