Login or Sign Up to become a member!
LessThanDot Sit Logo

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

Lessthandot twitter Lessthandot Linkedin Lessthandot friendfeed Lessthandot facebook Lessthandot rss

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

Your profile

    Search

    XML Feeds

    Google Ads

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

    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)

    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

    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

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

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

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

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

    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

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

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

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

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

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

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

    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.

    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

    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.

    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

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

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

    About the Author

    User bio imageDenis 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 SitingsTwitterFacebookLinkedInHomePageFlickrLTD RSS Feed
    8941 views
    InstapaperVote on HN

    2 comments

    Comment from: Rob Crawford [Visitor]
    Rob Crawford 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] Email
    SQLDenis 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

    Leave a comment


    Your email address will not be revealed on this site.

    To mislead the spambots.

    Your URL will be displayed.
    (Line breaks become <br />)
    (Name, email & website)
    (Allow users to contact you through a message form (your email will not be revealed.)