什么是十六进制格式的日期在SQL服务器的规格？ [英] What is the specification of Hexadecimal Date format in SQL server?

问题描述

的数据类型日期到下面的字符串SQL Server Management Studio中产生的价值： CAST（0x38320B00如日期）。

SQL Server Management studio generated value of datatype "Date" into following string: CAST(0x38320B00 AS Date).

我需要将其转换成传统的.NET日期时间（我有字符串在C＃应用程序）。我知道，如果它是SQL Server的日期时间将是2倍长六角号和第一部分将从1900年1月1日指定天数，和第二部分将从中午指定的1 / 1/300秒号码。

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.

我觉得分别在SQL Server日期数据类型，这将是日期时间只是第一部分（部分时间省略），但是事实并非如此。当我尝试下面的代码片段，我得到异常：

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?

推荐答案

在 DATE 类型在内部存储为3个字节的整数，再presenting天数自1月1日0001。

The DATE type is stored internally as a 3-byte integer, representing the number of days since 1 January 0001.

这是你的十六进制值在小尾数格式，所以你需要翻转成大端之前，你可以用它在C＃的DateTime 计算

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

这篇关于什么是十六进制格式的日期在SQL服务器的规格？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！