none
DATETIME2 Internal Storage Structure

    Question

  • I propose the following:

    DATETIME2 8 bytes (precision > 4) internal storage structure

    o 1st byte: precision like 7

    o middle 4 bytes: number of time units (smallest 100ns) since midnight

    o last 3 bytes: number of days after the base date 0001-01-01

    Is it correct? Thanks.

    DECLARE @dt2 DATETIME2 = '00020101 00:00:00.0000009'
    SELECT convert(binary(9), @dt2);
    -- 0x0709000000006D0100
    
    DECLARE @dt26 DATETIME2(6) = '00020101 00:00:00.000008'
    SELECT convert(binary(9), @dt26);
    -- 0x0608000000006D0100


    Kalman Toth Database & OLAP Architect SELECT Video Tutorials 4 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    Wednesday, February 12, 2014 6:21 PM
    Moderator

All replies

  • Yes, you're right on that one.

    1st byte is the precision. But depending on precision, that is amount of decimals per second. Highest precision is 100ns, that is 1/10.000.000 second, lowest is 1 second.

    If precision is 0-2 the size is 6 byte, 3-4 the size is 7, and 5-7 size is 8.

    The last 3 bytes are always the amount of Days from 0001-01-01.

    the 2, 3 or 4 bytes in between is the amount of timeunits defined by the precision on that date.

    the bytes are in shifted order.

    for example:

    The datetime2(7) "2001 00:00:00.0000000" is stored as "0x07 00 00 00 00 00 75 25 0B"

    The last Three bytes is 75 25 0B and should be read 0B 25 75 in hex which is 730485 Days after 0001-01-01

    Try this:

    USE tempdb;
    GO
    
    CREATE TABLE dbo.x
    (
     d0 DATETIME2(0)  NULL, 
     d1 DATETIME2(1)  NULL, 
     d2 DATETIME2(2)  NULL, 
     d3 DATETIME2(3)  NULL, 
     d4 DATETIME2(4)  NULL, 
     d5 DATETIME2(5)  NULL, 
     d6 DATETIME2(6)  NULL, 
     d7 DATETIME2(7)  NULL, 
     v1 BINARY(32) NULL
    );
    
    declare @d datetime2(7) = '0001-01-01 00:00:00.0000000';
    
    INSERT dbo.x(d0, d1, d2, d3, d4, d5, d6, d7, v1)
    SELECT @d, @d, @d, @d, @d, @d, @d, @d, CONVERT(VARBINARY(32), @d);
    
    SET @d = '0001-01-01 00:00:00.0000001'
    
    INSERT dbo.x(d0, d1, d2, d3, d4, d5, d6, d7, v1)
    SELECT @d, @d, @d, @d, @d, @d, @d, @d, CONVERT(VARBINARY(32), @d);
    
    SET @d = '0001-01-01 00:00:00.0000010'
    
    INSERT dbo.x(d0, d1, d2, d3, d4, d5, d6, d7, v1)
    SELECT @d, @d, @d, @d, @d, @d, @d, @d, CONVERT(VARBINARY(32), @d);
    
    SET @d = '0001-01-01 00:00:00.0000100'
    
    INSERT dbo.x(d0, d1, d2, d3, d4, d5, d6, d7, v1)
    SELECT @d, @d, @d, @d, @d, @d, @d, @d, CONVERT(VARBINARY(32), @d);
    
    SET @d = '0001-01-01 00:00:00.0001000'
    
    INSERT dbo.x(d0, d1, d2, d3, d4, d5, d6, d7, v1)
    SELECT @d, @d, @d, @d, @d, @d, @d, @d, CONVERT(VARBINARY(32), @d);
    
    SET @d = '0001-01-01 00:00:00.0010000'
    
    INSERT dbo.x(d0, d1, d2, d3, d4, d5, d6, d7, v1)
    SELECT @d, @d, @d, @d, @d, @d, @d, @d, CONVERT(VARBINARY(32), @d);
    
    SET @d = '0001-01-01 00:00:00.0100000'
    
    INSERT dbo.x(d0, d1, d2, d3, d4, d5, d6, d7, v1)
    SELECT @d, @d, @d, @d, @d, @d, @d, @d, CONVERT(VARBINARY(32), @d);
    
    SET @d = '0001-01-01 00:00:00.1000000'
    
    INSERT dbo.x(d0, d1, d2, d3, d4, d5, d6, d7, v1)
    SELECT @d, @d, @d, @d, @d, @d, @d, @d, CONVERT(VARBINARY(32), @d);
    
    SET @d = '0001-01-01 00:00:01.0000000'
    
    INSERT dbo.x(d0, d1, d2, d3, d4, d5, d6, d7, v1)
    SELECT @d, @d, @d, @d, @d, @d, @d, @d, CONVERT(VARBINARY(32), @d);
    
    SET @d = '0001-01-01 00:01:00.0000000'
    
    INSERT dbo.x(d0, d1, d2, d3, d4, d5, d6, d7, v1)
    SELECT @d, @d, @d, @d, @d, @d, @d, @d, CONVERT(VARBINARY(32), @d);
    
    SET @d = '0001-01-01 00:10:00.0000000'
    
    INSERT dbo.x(d0, d1, d2, d3, d4, d5, d6, d7, v1)
    SELECT @d, @d, @d, @d, @d, @d, @d, @d, CONVERT(VARBINARY(32), @d);
    
    SET @d = '0001-01-01 01:00:00.0000000'
    
    INSERT dbo.x(d0, d1, d2, d3, d4, d5, d6, d7, v1)
    SELECT @d, @d, @d, @d, @d, @d, @d, @d, CONVERT(VARBINARY(32), @d);
    
    SET @d = '0001-01-01 01:00:00.0000000'
    
    INSERT dbo.x(d0, d1, d2, d3, d4, d5, d6, d7, v1)
    SELECT @d, @d, @d, @d, @d, @d, @d, @d, CONVERT(VARBINARY(32), @d);
    
    SET @d = '0001-01-01 10:00:00.0000000'
    
    INSERT dbo.x(d0, d1, d2, d3, d4, d5, d6, d7, v1)
    SELECT @d, @d, @d, @d, @d, @d, @d, @d, CONVERT(VARBINARY(32), @d);
    
    SET @d = '0001-01-02 00:00:00.0000000'
    
    INSERT dbo.x(d0, d1, d2, d3, d4, d5, d6, d7, v1)
    SELECT @d, @d, @d, @d, @d, @d, @d, @d, CONVERT(VARBINARY(32), @d);
    
    SET @d = '0001-01-10 00:00:00.0000000'
    
    INSERT dbo.x(d0, d1, d2, d3, d4, d5, d6, d7, v1)
    SELECT @d, @d, @d, @d, @d, @d, @d, @d, CONVERT(VARBINARY(32), @d);
    
    SET @d = '0001-01-11 00:00:00.0000000'
    
    INSERT dbo.x(d0, d1, d2, d3, d4, d5, d6, d7, v1)
    SELECT @d, @d, @d, @d, @d, @d, @d, @d, CONVERT(VARBINARY(32), @d);
    
    SET @d = '0002-01-01 00:00:00.0000000'
    
    INSERT dbo.x(d0, d1, d2, d3, d4, d5, d6, d7, v1)
    SELECT @d, @d, @d, @d, @d, @d, @d, @d, CONVERT(VARBINARY(32), @d);
    
    SET @d = '0010-01-01 00:00:00.0000000'
    
    INSERT dbo.x(d0, d1, d2, d3, d4, d5, d6, d7, v1)
    SELECT @d, @d, @d, @d, @d, @d, @d, @d, CONVERT(VARBINARY(32), @d);
    
    SET @d = '0100-01-01 00:00:00.0000000'
    
    INSERT dbo.x(d0, d1, d2, d3, d4, d5, d6, d7, v1)
    SELECT @d, @d, @d, @d, @d, @d, @d, @d, CONVERT(VARBINARY(32), @d);
    
    SET @d = '1000-01-01 00:00:00.0000000'
    
    INSERT dbo.x(d0, d1, d2, d3, d4, d5, d6, d7, v1)
    SELECT @d, @d, @d, @d, @d, @d, @d, @d, CONVERT(VARBINARY(32), @d);
    
    SET @d = '1001-01-01 00:00:00.0000000'
    
    INSERT dbo.x(d0, d1, d2, d3, d4, d5, d6, d7, v1)
    SELECT @d, @d, @d, @d, @d, @d, @d, @d, CONVERT(VARBINARY(32), @d);
    
    SET @d = '2001-01-01 00:00:00.0000000'
    
    INSERT dbo.x(d0, d1, d2, d3, d4, d5, d6, d7, v1)
    SELECT @d, @d, @d, @d, @d, @d, @d, @d, CONVERT(VARBINARY(32), @d);
    
    SELECT d7 AS datetime2_7, DATALENGTH(d0) d0, CONVERT(VARBINARY(32), d0) b0, DATALENGTH(d1) d1, CONVERT(VARBINARY(32), d1) b1, DATALENGTH(d2) d2, CONVERT(VARBINARY(32), d2) b2, DATALENGTH(d3) d3, CONVERT(VARBINARY(32), d3) b3, DATALENGTH(d4) d4, CONVERT(VARBINARY(32), d4) b4, DATALENGTH(d5) d5, CONVERT(VARBINARY(32), d5) b5, DATALENGTH(d6) d6, CONVERT(VARBINARY(32), d6) b6, DATALENGTH(d7) d7, CONVERT(VARBINARY(32), d7) b7 FROM dbo.x;
    
    DROP TABLE dbo.x;
    In the resultset you see different timestamps in different precisions in binary format.


    Microsoft Certified Trainer & MVP on SQL Server
    Please "Propose as Answer" if you got an answer on your question, and vote for it as helpful to help other user's find a solution on a similar question quicker.


    Friday, July 18, 2014 3:38 PM