CODEWITHSUNDEEP
sqlsql-server
amit agarwal
amit agarwal

Reputation: 93

How to convert local (est ) column timezone to UTC time zone in Sql Server

We have multi columns in sql in local time zones like EST ,PST , CST and so on . We would like convert all these time zones in UTC time zones considering Day light savings into account and save it to another column.

I tried below query but it converts UTC to local time zone (EST) but I am looking for EST to UTC time zone.

select CONVERT(datetime, SWITCHOFFSET(dateTimeField, DATEPART(TZOFFSET, dateTimeField AT TIME ZONE 'Eastern Standard Time')))

Upvotes: 1

Views: 3420

Answers (1)

Ben Thul
Ben Thul

Reputation: 32717

Based on a comment above, you should be able to use the following example to get what you want.

DECLARE @ts DATETIME = GETDATE();

SELECT
    @ts AT TIME ZONE 'Pacific Standard Time',
    @ts AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE 'UTC',
    CAST(@ts AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE 'UTC' AS DATETIME)

What I'm showing here is the various steps. In turn:

  1. Getting an arbitrary datetime value. This could easily be a column from your table.
  2. Telling SQL "this datetime is in the PST time zone"
  3. Telling SQL "convert this PST time to a UTC time"
  4. Casting the resulting UTC time back to a datetime (presumably so it will be able to be inserted back into the table)

To be explicit about "what would this look like if I wanted to convert the data in my table?", it'd look something like this:

alter table dbo.yourTable add 
   isConverted bit
   constraint DF_yourTable_isConverted default 0;

update top(1000) dbo.yourTable
set yourColumn = CAST(
      yourColumn AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE 'UTC' 
      AS DATETIME
   ),
   isConverted = 1
where isConverted = 0;

Note I've added a column whose purpose is to provide a notion of "has this particular row's column been converted?" so you don't accidentally convert a given value twice. Once all the data in the table is converted, you can drop the column.

Upvotes: 3

Related Questions