CODEWITHSUNDEEP
sql-server-2008t-sql
Randy Minder
Randy Minder

Reputation: 48482

Not Sure How To Convert to DateTimeOffset Correctly

We have a table that contains a DateTime2(7) column, and we're trying to take the values from that column and insert them into another table whose respective column data type is DateTimeOffset. The source dates look something like this:

2013-02-28 00:15:49.8270000
2013-03-11 00:26:38.1270000

We want them converted to look like this (assuming Eastern Standard Time - Time moves forward one hour on March 10.)

2013-02-28 00:15:49.8270000 -05:00
2013-03-11 00:26:38.1270000 -04:00

I'm not sure how to tell SQL Server to take the source date, and, based on that date and time, convert it to the proper DateTimeOffset in affect at that date and time, using EST time zone.

I'm aware of the ToDateTimeOffset function, but, if I understand correctly, I have to supply that function an offset value. I want SQL Server to figure this out, based on a time zone (e.g., EST) I provide it.

Upvotes: 0

Views: 180

Answers (1)

Richard Deeming
Richard Deeming

Reputation: 31228

This should be fairly simple to do with SQLCLR. Assuming the time zone is always EST, something like this should work:

using System;
using Microsoft.SqlServer.Server;

namespace SqlUtilities
{
   public static class Dates
   {
      private static readonly TimeZoneInfo EST = TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time");

      [SqlFunction(
         Name = "ToDateTimeOffset",
         DataAccess = DataAccessKind.None,
         IsDeterministic = true,
         IsPrecise = true
      )]
      public static DateTimeOffset? ToDateTimeOffset(DateTime? value)
      {
         if (value == null) return null;

         var source = value.Value;
         var offset = EST.GetUtcOffset(source);
         return new DateTimeOffset(source, offset);
      }
   }
}

Upvotes: 1

Related Questions