SQL Server中十六进制日期格式的规范是什么？

发布于 2024-08-04 06:00:26 字数 491 浏览 2 评论 0原文

SQL Server Management Studio 将数据类型“Date”的值生成为以下字符串： CAST（0x38320B00 AS 日期）。

我需要将其转换为经典的 .NET 日期时间（我在 C# 应用程序中有该字符串）。我知道，如果它是 SQL Server DateTime，它将是十六进制数的两倍，第一部分将指定从 1.1.1900 开始的天数，第二部分将指定从中午开始的 1/300 秒数。

我认为在 SQL Server Date 数据类型中，这只是 DateTime 的第一部分（时间部分被省略），但事实并非如此。当我尝试以下代码片段时，我得到异常：

Int32 high = Int32.Parse("38320B00", NumberStyles.HexNumber);
DateTime start = new DateTime(1900, 1, 1);
start = start.AddDays(high);

那么这个数字指定了什么？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

冰火雁神 2024-08-11 06:00:26

DATE 类型在内部存储为一个 3 字节整数，表示自 0001 年 1 月 1 日以来的天数。

您拥有的十六进制值采用小端格式，因此您需要将其转换为大端格式，然后才能在 C# 中使用它 日期时间计算：

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

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

回复收藏 0 原文

~没有更多了~