CODEWITHSUNDEEP
sqlsql-serverdatetime
Ren
Ren

Reputation: 437

Convert a given datetime to UTC time based on time zone description in SQL

I have data like the following. The first column is of the type datetime, and the second column is the corresponding time zone info, and is of the type nvarchar.

07/04/2012 14:20:45   EASTERN STANDARD TIME
07/04/2012 13:55:10   INDIAN STANDARD TIME
07/04/2012 10:55:00   CENTRAL STANDARD TIME

How can I convert the datetime column value of all the rows to UTC time?

Upvotes: 1

Views: 515

Answers (2)

Ren
Ren

Reputation: 437

This is how I could solve: CLR SQL

http://weblogs.asp.net/farhank/archive/2008/07/04/creating-clr-sql-user-defined-function-to-validate-values-using-regular-expression.aspx

(configuring the db) http://exacthelp.blogspot.in/2012/02/create-assembly-for-assembly-failed.html

Upvotes: 1

Igor Borisenko
Igor Borisenko

Reputation: 3866

Try this

UPDATE dbo.YourTable
SET DateTimeColumn=CASE TimeShift 
                        WHEN N'EASTERN STANDARD TIME'
                             THEN DATEADD(hour,EasternTimeShift,DateTimeColumn)
                        WHEN N'INDIAN STANDARD TIME'
                             THEN DATEADD(hour,IndianTimeShift,DateTimeColumn)
                        ...
                        ELSE DateTimeColumn
                    END

Upvotes: 1

Related Questions