Geek Answers Handbook

What SQL Server SQL Data Type Is Used to Save UTC Time

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

The program loads the date from the XML file into the database and at some point later checks to see if the data in XML is the same as the data in the database ... The check failed when the ReceivedDate dates in the XML and the database do not match. For instance:

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 the date from the database does not have the Kind property set in Utc, and the number of ticks is much less, and therefore the date comparison fails ...

  • How to save the full UTC date to SQL server, so when Entity Framework retrieves it, I get a System.DateTime value that is exactly the same as one from the XML file (including Kind = Utc)?
  • Is it just a matter of using a different sql data type for my column (e.g. datetime2 istead of datetime)?

Update:

How I solved it:

  • change sql data type to "datetime2" to combine precision between sql data type and .net System.DateTime
  • in my POCO, I redefined Equals and, checking the ReceivedDate property, I just created another DateTime variable from ReceivedDate, but using the constructor with Kind == Utc.

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

+5
3

datetime Sql .

.Net . , . , 0.0012217 .

, datetime2:

0 7 100 . - 7 .

datetime2 , .Net datetime.

0

datetimeoffset SQL Server.

, , 24- .

datetimeoffset DateTime .NET.

+7

If you want to rotate your UTC database DateTimein C # DateTimes, do it. When you create DateTime, use the constructor with the parameter DateTimeKind:

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

If you're looking for the best way to actually save it in SQL (and you in 2008), follow @Oded's tips.

0
source

More articles:


All Articles