how to convert all datetime columns in a sql server 2005 express database with data to UTC

Question

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) } } }

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
– Gullu, Commented Jul 15, 2011 at 19:16
The SQL Server datetime type doesn't appear to capture timezone info: are all the datetime values you're converting to UTC in the same / known timezone? — Dan J
– Dan J, Commented Jul 15, 2011 at 19:25

Aaron Bertrand · Accepted Answer · 2021-12-28 03:50:29Z

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.

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.

Collectives™ on Stack Overflow

how to convert all datetime columns in a sql server 2005 express database with data to UTC

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related