14 July 2010

UTC gotchas in .NET and SQL Server

After doing some work with DateTime recently I stumbled across the interesting behaviour that a DateTime which is DateTimeKind.Unspecified will be treated as a DateTimeKind.Local whenever you try to perform some operation upon it. You get an “unspecified” DateTime whenever you don’t explicitly say it is Utc or Local. This makes sense because, when you do the following, in most cases what you intended was to use local time:

DateTime d1 = new DateTime(2010, 07, 01, 12, 0 ,0, 0);

If the current timezone is UTC +01:00 here's what I get when working with the DateTime created above:

d1.Kind; // => Unspecified
d1; // => 01/07/2010 12:00:00
d1.ToUniversalTime(); // => 01/07/2010 11:00:00
TimeZoneInfo.Local.GetUtcOffset(d1); // => 01:00:00

Note it’s applied an offset when calculating the UTC value which as we can see for clarification is +1 hour.

If what we actually wanted was a UTC time we need to explicitly specify the kind e.g.

DateTime d2 = new DateTime(2010, 07, 01, 12, 0 ,0, 0, DateTimeKind.Utc);
DateTime d2 = DateTime.UtcNow;

If you need to work with timezones other than UTC or the system timezone then you'll want to use DateTimeOffset rather than DateTime.

SQL Server and SqlDataReader

Another interesting gotcha arising from this is that the SQL Server datetime data type is also timezone agnostic. Any datetime values retrieved through the SqlDataReader will be an “unspecified” kind DateTime. This means that, even if you're correctly using the C# DateTime.UtcNow or the SQL GETUTCDATE() to produce the values in the database, when you try to retrieve them they will be shifted incorrectly according to the local timezone. Yikes!

There are two ways to deal with this.

DateTime.SpecifyKind()

The first is in C# using DateTime.SpecifyKind():

DateTime d3 = DateTime.SpecifyKind(d1, DateTimeKind.Utc);
d3.Kind; // => Utc
d3; // => 01/07/2010 12:00:00
d3.ToUniversalTime(); // => 01/07/2010 12:00:00

Which could be wrapped up in an extension method for ease of use e.g.

public static class SqlDataReaderExtensions
{
 public static DateTime GetDateTimeUtc(this SqlDataReader reader, string name)
 {
  int fieldOrdinal = reader.GetOrdinal(name);
  DateTime unspecified = reader.GetDateTime(fieldOrdinal);
  return DateTime.SpecifyKind(unspecified, DateTimeKind.Utc);
 }
}

SQL Server 2008 datetimeoffset

If you're using SQL Server 2008 you have the option of using the datetimeoffset data type instead. This will store the +00:00 timezone internally and the SqlDataReader will then retrieve the value correctly as a DateTimeOffset. No need to muck about with Kind.

If you have an existing database using datetime you can CAST these as a datetimeoffset in your query which usefully uses an offset of +00:00 in this case. (It treats "unspecified" as UTC – tut!)

2 comments:

Derek Fowler said...

In answer to the guy who posted a comment with a rather politically sensitive message at the end of it...

--
what if you pass datetime from client to sql server?

Now since .net Datetime has a kind member, so when you pass it to server, will the value be converted to local one before it is passed to and stored at server?
--

If you pass in a DateTime the Kind is ignored and no timezone offset on the database server itself is applied either. When you try to retrieve the value it will come back as Kind Unspecified regardless of the Kind when it was inserted. As I said in the post it is this you have to watch out for, if you've inserted a date from one timezone and are retrieving it in another.

Scott said...

Excellent, thanks. I was just working through the same problem with multiple layers. DB layer, DTO layer, JSON layer and loads of conversions going on imbetween. I think I need to change the datalayer to ensure the kind is getting set correctly.