Quick Fix: SQL datetime

Setup:

I was importing some records from system A to system B.  Both were using MS SQL 2008 databases.  In my stored procedure there is an insert … select … statement.

Problem:

It turns out that system A stores the date_time field for the record in UTC time and then the application displays it as local time (Central time zone in this case).  System B is less robust and assumes the date_time matches whatever the local server time is.  All users are shown records in server time, wherever the user may be located.  Depending on the setup this usually never happens.  Let’s just say I’m aware of the shortcomings of this method and I’ll leave out the cliche “if I had been in charge I would have …” comment.

This was brought to my attention by a customer who said that their system said the record occurred at August 1st at 1 PM, but the new system said it occurred August 1st at 6 PM.  This wasn’t acceptable because they needed the info to match to be sure they brought up the same record in both systems.

Fixed:

I didn’t want to hard code anything in my stored procedure like “subtract 5 hours” to convert UTC to local time.  This would work for 9 months out of the year, but when daylight savings time ends CST is -6 hours from UTC.  DST changes from year to year, and sometimes the congress changes it all together and I don’t want to build that complexity into a dumb stored procedure.

First, you can get the difference in hours between UTC and local time with this:

select datediff(hour,GETUTCDATE(),GETDATE())

Then you can use dateadd to add that offset when you are inserting from A to B:

insert into A.dbo.records
date_time
select
dateadd(hour,datediff(hour,GETUTCDATE(),GETDATE()),utc_recs.record_date) as date_time
from B.dbo.utc_records utc_recs

This is greatly stripped down of course to show just the salient point.  I hope this saves someone some time in the future.

Steven Gangstead

Note: The only site I had to consult was the msdn library on date and time functions.