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).
Inserting a UTC datetime into a datetimeoffset column If you're using GETUTCDATE() to get the UTC datetime, it won't have the timezone offset appended to it. But if you're inserting into a datetimeoffset column, then it will put the UTC timezone offset for you. It automatically appended the +00:00 timezone offset.
SQL Server GETUTCDATE() Function The GETUTCDATE() function returns the current database system UTC date and time, in a 'YYYY-MM-DD hh:mm:ss.
If you store a DateTime object to the DB with a DateTimeKind of either Utc or Local , when you read that record back from the DB you'll get a DateTime object whose kind is Unspecified . Basically, it appears the Kind property isn't persisted.
SELECT CONVERT(datetime, SWITCHOFFSET(CONVERT(DATETIMEOFFSET, GETUTCDATE()), DATENAME(TZOFFSET, SYSDATETIMEOFFSET()))) AS LOCAL_IST; Here, the GETUTCDATE() function can be used to get the current date and time UTC. Using this query the UTC gets converted to local IST.
The generated LinqToSql code provides extensibility points, so you can set values when the objects are loaded.
The key is to create a partial class which extends the generated class, and then implement the OnLoaded
partial method.
For instance, let's say your class is Person
, so you have a generated partial Person
class in Blah.designer.cs
.
Extend the partial class by creating a new class (must be in a different file), as follows:
public partial class Person { partial void OnLoaded() { this._BirthDate = DateTime.SpecifyKind(this._BirthDate, DateTimeKind.Utc); } }
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With