1

background: We recently upgraded to handle UTC. ie., went thru each sp and func and changed the t-sql code to handle utc. For eg., changed getdate() to getutcdate() etc. For new shipments we are good where the db is blank. For our older customers who want to keep their data we need to run a script that will update all the dates to utc. I am about to code in t-sql something like below and want to know if this is best way. Below is pseudo code. thanks

foreach (table in mydb.tables())
{
  foreach (column col in table)
  {
      if (col.type == datetime)
      {
           update table set col = fn_convert_date_to_utc(col)
      }
  }
}
Gullu
  • 3,477
  • 7
  • 43
  • 70
  • Why are you doing this with C# code? – JonH Jul 15 '11 at 19:13
  • more familiar with c# than t-sql and it also is easier to read psuedo code like. I will edit and specify that this is psudeo code. thx – Gullu Jul 15 '11 at 19:16
  • The [SQL Server datetime type](http://msdn.microsoft.com/en-us/library/ms187819.aspx) doesn't appear to capture timezone info: are all the datetime values you're converting to UTC in the same / known timezone? – Dan J Jul 15 '11 at 19:25
  • @djacobson. yes. All in same time zone. thx – Gullu Jul 15 '11 at 20:44

1 Answers1

1

Assuming you know the offset between UTC and the timezone the data is stored in, it's pretty simple:

DECLARE @offset INT;
SET @offset = <offset>;
UPDATE table SET col = DATEADD(HOUR, @offset, col);

Note that might be negative or positive, I have no idea which side of Greenwich you are.

Of course this gets more complicated if you are in a timezone that observes daylight saving time; in this case you may need a more expansive solution such as using a calendar table. This is particularly complex if your data extends back before George Bush changed the American DST rules, for example. I do have an article from a long time ago that may be useful; a more recent series is here:

Also if any of your data falls in that window between 12:00 AM and 2:00 AM on a spring forward/fall back day, where I am never sure whether it is right to change it because it's the changeover day or to not change it because it's before 2 AM.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • thx Aaron. I am in much better position to do this with the info you have provided. Never used calendar tables before but something new to learn. – Gullu Jul 16 '11 at 22:45