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

Assuming you know the offset between UTC and the timezone the data is stored in, it's pretty simple: DECLARE @offset INT; SET @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 savings; 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. 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.

Assuming you know the offset between UTC and the timezone the data is stored in, it's pretty simple: DECLARE @offset INT; SET @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 savings; 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.

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.

– Gullu Jul 16 at 22:45 Calendar tables can be very powerful, especially if you have to deal with rules for different countries, holidays for different companies, vacations for certain divisions, etc. I wrote some examples here, including using a calendar table to determine the offset given a time zone and a date (though I think it was written before the George Bush DST shift - it's not my site anymore, so I can't update it): sqlserver2000.databases.aspfaq. Com/… – Aaron Bertrand Jul 17 at 0:54.

How to convert all datetime columns in a sql server 2005 express database with data to UTC - Stack Overflow.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.

Related Questions