Convert values stored in a local server time column (default GETDATE()) to UTC time

I am upgrading a legacy application that used GETDATE() on columns (describing the row created date) in many tables.
  1. We rename the column (our column names indicate local or UTC)
  2. Set the default to now be GETUTCDATE()
  3. Convert the old values.
It then becomes quite trivial to update the legacy values (step 3) to what they would have been if this had been the original default by comparing the server time and UTC time and deriving a difference:UPDATE [table] SET [new_utc_column] = DATEADD(second,DATEDIFF(second,GETUTCDATE(),GETDATE()),[new_utc_column])This is using the difference between the current local and current UTC  as it exists today. I have not found a way to determine what the offset would have been for the date in question but this is close enough in our case.

Comments Closed.