Which SQL Server sql data type to use in order to preserve UTC date time

Question

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.

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:

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...

• 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)?
• Is this just a matter of using different sql data type for my column (e.g. datetime2 istead of datetime)?

---

Update:

The way I resolved this was to:

1. change sql data type to "datetime2" to match precision between sql data type and .net System.DateTime
2. 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.

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

Answer 1

Use datetimeoffset to store on 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.

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

Answer 2

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

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.

If possible, you could use the datetime2:

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

datetime2 uses the same accuracy as the .Net DateTime.