使用哪种SQL Server sql数据类型以保留UTC日期时间

I have a SQL Server DB table that has a column "ReceivedDate" defined as "datetime" which should contain UTC date...In my C# code I use Entity Framework to map to table to a class, which has a corresponding property "ReceivedDate" of type System.DateTime.

我有一个SQL Server数据库表,其中“ReceivedDate”列定义为“datetime”,应该包含UTC日期......在我的C#代码中,我使用Entity Framework将表映射到一个类,该类具有相应的属性“ReceivedDate” “System.DateTime类型。

The program loads the date from an XML file into DB and at some point later checks if the data in XML is same as data in DB...The check fails when dates of ReceivedDate in XML and DB don't match...For example:

程序将日期从XML文件加载到DB中,稍后检查XML中的数据是否与DB中的数据相同...当XML和DB中的ReceivedDate的日期不匹配时,检查失败...例:

ReceivedDate from XML:
<ReceivedDate>2010-12-16T22:53:27.5912217Z</ReceivedDate>

ReceivedDate from DB:
2010-12-16 22:53:27.590

After some debugging I noticed that date from DB does not have Kind property set to Utc and the number of ticks is much less and therefore comparison on dates fails...

经过一些调试后,我注意到DB中的日期没有将Kind属性设置为Utc,并且ticks的数量要少得多,因此日期比较失败......

How do I store full UTC date in SQL server so when Entity Framework retrieves it, I get System.DateTime value that is exactly same as the one from XML file (including Kind=Utc)?

如何在SQL服务器中存储完整的UTC日期,以便当Entity Framework检索它时,我得到的System.DateTime值与XML文件中的值完全相同(包括Kind = Utc)?

Is this just a matter of using different sql data type for my column (e.g. datetime2 istead of datetime)?

这只是为我的列使用不同的sql数据类型(例如datetime2而不是datetime)吗?

Update:

The way I resolved this was to:

我解决这个问题的方法是:

change sql data type to "datetime2" to match precision between sql data type and .net System.DateTime

将sql数据类型更改为“datetime2”以匹配sql数据类型和.net System.DateTime之间的精度

in my POCO I overrode Equals and when checking ReceivedDate property I just created another DateTime variable from ReceivedDate but using constructor with Kind == Utc.

在我的POCO中,我覆盖了Equals,当检查ReceivedDate属性时,我刚刚从ReceivedDate创建了另一个DateTime变量,但是使用了Kind == Utc的构造函数。

This works, although I do agree that using DateTimeOffset would probably be better solution.

这是有效的,虽然我同意使用DateTimeOffset可能是更好的解决方案。

3 个解决方案

#1

The datetime in Sql server only has an accuracy of one three-hundredth of a second.

Sql server中的日期时间只有三百分之一秒的精度。

The accuracy in .Net is higher. The DB data is therefore rounded, and not the same. If you look at your data, the error is 0.0012217 seconds.

.Net的准确性更高。因此,DB数据是四舍五入的,而不是相同的。如果查看数据,则错误为0.0012217秒。

If possible, you could use the datetime2:

如果可能,您可以使用datetime2:

0 to 7 digits, with an accuracy of 100ns. The default precision is 7 digits.

0到7位数,精度为100ns。默认精度为7位数。

datetime2 uses the same accuracy as the .Net DateTime.

datetime2使用与.Net DateTime相同的精度。

#2

Use datetimeoffset to store on SQL Server.

使用datetimeoffset存储在SQL Server上。

Defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock.

定义一个日期,该日期与具有时区感知且基于24小时制的一天的时间相结合。

Also, consider using the DateTimeOffset structure instead of DateTime in your .NET code.

另外,请考虑在.NET代码中使用DateTimeOffset结构而不是DateTime。

#3

If you want to turn your database UTC DateTimes into C# DateTimes, do this. When you create the DateTime, use the constructor with the DateTimeKind parameter:

如果要将数据库UTC DateTimes转换为C#DateTimes,请执行此操作。创建DateTime时,请使用带有DateTimeKind参数的构造函数:

DateTime utcDateTime = new DateTime(((DateTime)row["myUtcDateTime"]).Ticks, DateTimeKind.Utc)

If you're looking for a better way to actually store it in SQL (and you're on 2008), follow @Oded's advice.

如果您正在寻找更好的方法将其实际存储在SQL中(并且您在2008年),请关注@ Oded的建议。

#1