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.

Your profile

Tools

Authors

Main Categories

Search

XML Feeds

RSS 2.0: Posts, Comments
Atom: Posts, Comments

What is RSS?

Google Ads

« Getting The Percentage Of NULLS And Values In A SQL Server Table

How Does Between Work With Dates In SQL Server? »

comments

How Are Dates Stored In SQL Server?

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

Instapaper

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

Line number Off | Hide | Select all

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

tsql

Line number Off | Hide | Select all

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

tsql

Line number Off | Hide | Select all

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

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

tsql

Line number Off | Hide | Select all

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

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

tsql

Line number Off | Hide | Select all

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

tsql

Line number Off | Hide | Select all

DECLARE @d DATETIME
SELECT @d = .0
SELECT @d
GO

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

tsql

Line number Off | Hide | Select all

DECLARE @d DATETIME
SELECT @d = .1
SELECT @d
GO

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

tsql

Line number Off | Hide | Select all

DECLARE @d DATETIME
SELECT @d = .12
SELECT @d
GO

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

tsql

Line number Off | Hide | Select all

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.

tsql

Line number Off | Hide | Select all

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

tsql

Line number Off | Hide | Select all

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.

tsql

Line number Off | Hide | Select all

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

tsql

Line number Off | Hide | Select all

DECLARE @d DATETIME
SELECT @d = 2958463
SELECT @d
GO

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

About the Author

Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.

Social Sitings

Article Views: 8941 views

2 comments

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

Comment feed for this post

LessThanDot

Your profile

Tools

Tag cloud

Authors

Main Categories

Search

XML Feeds

Google Ads

How Are Dates Stored In SQL Server?

About the Author

2 comments

Leave a comment

LessThanDot

LTD Social Sitings

Your profile

Tools

Tag cloud

Authors

Main Categories

Search

XML Feeds

Google Ads

How Are Dates Stored In SQL Server?

About the Author

2 comments

Leave a comment