How to convert 8 byte datetime from fn_dblog() details in [Log Content 0] into a C# DateTime object?

Question

I've deleted a row of data that was inserted recently. Rather than restore and roll forward a second copy of this huge DB to retrieve the inserted data, I'm trying to use the fn_dblog() "undocumented" system function to retrieve it. Using a description (found here: https://sqlfascination.com/2010/02/03/how-do-you-decode-a-simple-entry-in-the-transaction-log-part-1/)

of the contents of the [Log Content 0] column fn_dblog() returns, I am successfully retrieving my inserted (and later deleted) data from the log file. In the section of this binary data reserved for fixed width column data, I found that the SQL DateTime column values take 8 bytes. I'm processing the binary data in a .NET program, using BitConverter.ToInt64 or BitConverter.ToInt32 as appropriate for the Int or BigInt values

I've managed to retrieve all the inserted column values I need except for the datetime columns...

I'm unclear how to interpret the 8 bytes of a SQL DateTime column as a C# DateTime object. If it helps, below is an example hex and Int64 version of the datetime 8 bytes retrieved from the transaction log data for a particular datetime.

DateTime (around 7/31/2020) in binary: 0xF030660009AC0000 (Endian reversed: 0x0000AC09006630F0)

as an Int64: 189154661380804

Any suggestions? This is internal SQL Server representation of a date, I'm not sure where to find doc on it...

score 1 · Accepted Answer · answered Sep 22 '20 at 02:12

I finally did discover the answer: The SQL DateTime stored as VARBINARY (similar to the bytes I'm reading from the transaction log) contains two integers. The first is the date part - number of days since 1/1/1900. It will be negative for earlier dates.

The second integer is the number of milliseconds since midnight, divided by 3.33333333.

Because the bytes are stored as a long and in reverse, the first 4 bytes of the 8 bytes in the buffer are the minutes, the second is the date.

So here is a code snippet I used to get the date. I'm running through the fixed length fields one at a time, keeping track of the current offset in the byte array... the variable ba is the byte array of the bytes in the [Log Content 0] column.

        int TimeInt;
        int DateInt;
        DateTime tmpDt;

        //initialize the starting point for datetime - 1/1/1900
        tmpDt = new DateTime(1900, 1, 1);
        // get the time portion of the SQL DateTime
        TimeInt = BitConverter.ToInt32(ba, currOffset);
        currOffset += 4;
        // get the date portion of the SQL DateTime
        DateInt = BitConverter.ToInt32(ba, currOffset);
        currOffset += 4;
        // Add the number of days since 1/1/1900
        tmpDt = tmpDt.AddDays(DateInt);
        // Add the number of milliseconds since midnight
        tmpDt = tmpDt.AddMilliseconds(TimeInt * 3.3333333);

How to convert 8 byte datetime from fn_dblog() details in [Log Content 0] into a C# DateTime object?

1 Answers1