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!)