CODEWITHSUNDEEP
sql-serverdatetimeutcsqlclrdatetime-conversion
sTodorov
sTodorov

Reputation: 5461

Sql Server Local to UTC conversion

I've been wrecking my head with this for the last day or so, and I hope someone here can shed some light.

I recently migrated to Amazon RDS SQL Server which is in UTC. However, all my times in the database are in the local time zone (Western Europe). My next challenge is to convert all those DateTimes to UTC.

The quickest way, I think, will be to do that in Sql Server directly, however, because there is no easy way to account for DST, I resorted to a user defined function in SQL CLR (seen from another answer here) which doesn't work at the moment. It currenlty, looks like this:

[Microsoft.SqlServer.Server.SqlFunction]
  public static SqlDateTime ToUniversal(SqlDateTime dt)
  {
    if (dt.IsNull)
    {
      return SqlDateTime.Null;
    }
    else
    {
      return dt.Value.ToUniversalTime();
    }
  }

The function, however, doesn't convert from local to universal time. Example, 

select creationtime, dbo.ToUnversal(CreationTime) as New from testtable

Returns the same two datetimes, which are also the original ones.

Why is that? Is it because Sql Server is already in UTC so it recognizes that and doesn't convert it further?

Additionally, if you have any other recommended approaches, please add them. I've already tried solving that with a little .NET application but it is very slow with my database of 120GB.

Upvotes: 0

Views: 521

Answers (1)

Maarten A
Maarten A

Reputation: 11

Bit late to the game, but oh well..

Remember, if you go this .NET way, you must do the conversion on the source server if source and destination are set to different (system-)timezones! .NET uses the timezone set on Windows on the exeuting machine.

IIRC, DateTimes from SQL would be given to .NET with DateTimeKind.Unspecified. If this is the case, DateTime.ToUniversalTime always assumes you give it a local time and should convert just fine.

However, should the system timezone match the source server timezone -being something else than UTC- and should the DateTimes incorrectly be coming back with DateTimeKind.Universal so ToUniversal does nothing, you can force the DateTimes to be converted by doing:

  [Microsoft.SqlServer.Server.SqlFunction]
  public static SqlDateTime ToUniversal(SqlDateTime dt)
  {
    if (dt.IsNull)
    {
      return SqlDateTime.Null;
    }
    else
    {
      // SpecifyKind changes the Kind property without changing the actual
      var tempDate = DateTime.SpecifyKind(dt.Value, DateTimeKind.Local);
      return tempDate.ToUniversalTime();
    }
  }

Upvotes: 1

Related Questions