SQL Server Management studio generated value of datatype "Date" into following string:
CAST(0x38320B00 AS Date).
I need to convert it into classical .NET datetime (i have the string in c# app). I know that if it were SQL Server DateTime it would be 2 times longer Hex number and first part would specify number of days from 1.1.1900, and second part would specify number of 1/300th seconds from the noon.
I thought that respectively in SQL Server Date datatype this would be just first part of DateTime (time part omitted) however it's not. When I try following snippet i get exception:
Int32 high = Int32.Parse("38320B00", NumberStyles.HexNumber);
DateTime start = new DateTime(1900, 1, 1);
start = start.AddDays(high);
So what does this number specify?
The DATE type is stored internally as a 3-byte integer, representing the number of days since 1 January 0001.
The hex value that you have is in little-endian format, so you'll need to flip it into big-endian before you can use it in C# DateTime calculations:
string hexString = "38320B00";
// convert the first 6 characters to bytes and combine them into an int
// we can ignore the final two characters because the DATE type is a
// 3-byte integer - the most-significant-byte should always be zero
int days = byte.Parse(hexString.Substring(0, 2), NumberStyles.HexNumber)
| byte.Parse(hexString.Substring(2, 2), NumberStyles.HexNumber) << 8
| byte.Parse(hexString.Substring(4, 2), NumberStyles.HexNumber) << 16;
DateTime dt = new DateTime(1, 1, 1).AddDays(days);
Console.WriteLine(dt); // 12/12/2009 00:00:00
