Linq to SQL DateTime values are local (Kind=Unspecified) - How do I make it UTC?

user_69766 0
Viewed 16788

Isn't there a (simple) way to tell Linq To SQL classes that a particular DateTime property should be considered as UTC (i.e. having the Kind property of the DateTime type to be Utc by default), or is there a 'clean' workaround?

The time zone on my app-server is not the same as the SQL 2005 Server (cannot change any), and none is UTC. When I persist a property of type DateTime to the dB I use the UTC value (so the value in the db column is UTC), but when I read the values back (using Linq To SQL) I get the .Kind property of the DateTime value to be 'Unspecified'.

The problem is that when I 'convert' it to UTC it is 4 hours off. This also means that when it is serialized it it ends up on the client side with a 4 hour wrong offset (since it is serialized using the UTC).

linq-to-sql datetime sql-server c#
8 Answers

SQL Server DateTime does not include any timezone or DateTimeKind information, therefore DateTime values retrieved from the database correctly have Kind = DateTimeKind.Unspecified.

If you want to make these times UTC, you should 'convert' them as follows:

DateTime utcDateTime = new DateTime(databaseDateTime.Ticks, DateTimeKind.Utc);

or the equivalent:

DateTime utcDateTime = DateTime.SpecifyKind(databaseDateTime, DateTimeKind.Utc);

I assume your problem is that you are attempting to convert them as follows:

DateTime utcDateTime = databaseDateTime.ToUniversalTime();

This may appear reasonable at first glance, but according to the MSDN documentation for DateTime.ToUniversalTime, when converting a DateTime whose Kind is Unspecified:

The current DateTime object is assumed to be a local time, and the conversion is performed as if Kind were Local.

This behavior is necessary for backwards compatibility with .NET 1.x, which didn't have a DateTime.Kind property.

Joe_130870

The only way I can think to do this would be to add a shim property in a partial class that does the translation...

Marc_Gravell_233540

This code snippet will allow you to convert the DateTimes (Kind=Unspecified) you get back from LINQ to SQL into UTC times without the times being affected.

TimeZoneInfo UTCTimeZone = TimeZoneInfo.FindSystemTimeZoneById("UTC");
DateTime utcTime = TimeZoneInfo.ConvertTimeToUtc(myLinq2SQLTime, UTCTimeZone);

There are probably cleaner ways to do this but I had this to hand and could test it quickly!

I am not sure if there is a way to get it working with LINQ to SQL classes transparently - you might want to look in the partial class and see if you can hook where the values are read/written.

user_617140

I you want UTC, TimeZone class can do it for you, if you want to convert between different timezones, than TimeZoneInfo is for you. exemple from my code with TimeZoneInfo:

TimeZoneInfo cet = TimeZoneInfo.FindSystemTimeZoneById("Central European Standard Time");
ac.add_datetime = TimeZoneInfo.ConvertTime(DateTime.Now, cet);
user_963000
Related