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
  • Hi Kalman,

    I know that this is old post, but (1) it is still open, and (2) the answer is not accurate! and (3) I got here from Google and other will see this in the future as well.

    You asked about the stored format while you checked and examine the result of "CONVERT to binary". this is a common mistake which I mentioned and confirm in my post. The answer to your question is NO. DateTime2 is fixed length data and the precision is not stored in the row data but is part of the column information.

    In short: The "CONVERT to binary" adds one extra byte (which include the precision data). This is mistake to examine the result of "CONVERT to binary", and assume that this is the same as the actual stored data! It is not accurate in variable-length data like varchar or nvarchar and it is not accurate in DateTime2 as well. The only way to examine and get the real stored format is examine the data itself using DBCC PAGE.

    You can get the answer to your question and all the information regarding DateTime2 stored format, including the confirmation that you should use DBCC PAGE instead of CONVERT to BINARY, in the post there: http://ariely.info/Blog/tabid/83/EntryId/162/Examine-how-DateTime2-type-stored-in-the-data-file.aspx

    I hope this is useful :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]

    Sunday, May 10, 2015 8:31 AM
    Moderator