How to convert a SqlServer DateTime to universal time using SQL

Question

I have a database with DateTime fields that are currently stored in local time. An upcoming project will require all these dates to be converted to universal time. Rather than writing a c# app to convert these times to universal time, I'd rather use available sqlserver/sql features to accurately convert these dates to universal time so I only need an update script. To be accurate, the conversion would need to account for Daylight savings time fluctuations, ect.

Answer 1

SQL Doesn't have anything built in for this; Two ways would be the C# application (you mentioned you don't want) or writing a really complicated update statement with something like:

UtcDate = DATEADD(hour, CASE WHEN OriginalDate BETWEEN x AND y THEN 4 WHEN OriginalDate BETWEEN x2 AND y2 THEN 5 ... END, OriginalDate)

Note - I'd recommend the C# app plus something like TZ4Net to handle the conversion.

Answer 2

A User Defined Function would allow you to write an SQL query that looks like this:

SELECT toUTC([MyDateColumn], [MyTimeZoneColumn]) FROM [MyTable]

Then you get universal times back from the server without a lot of ugly syntax in the query itself. Now you could build the UDF for this with regular SQL similar to what Chris posted, but SQL Server 2005 and later will let you build the UDF using CLR (.Net: C# optional) instead. It has much better support for dates and can do a better job taking timezones and daylight savings time into account.

Answer 3

check out the convert function and the getutcdate function?

http://msdn.microsoft.com/en-us/library/ms187928.aspx