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...
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);