CODEWITHSUNDEEP
sql-servertimezone
Kiran Vallabhneni
Kiran Vallabhneni

Reputation: 1

Convert different timezones to UTC Date

I have a table that has date time column and timezone column in a SQL Server table. I am now trying to convert the date time from different time zones to UTC date and load the data into a new table and remove the timezone column.

For example:

Date time.              TIMEZONE
1/1/2020 10:34 AM       EST
1/2/2020 08:26 AM.      CST

New table:

Date time 
1/1/2020 6:34 pm
1/2/2020 4:26 pm

Upvotes: 0

Views: 170

Answers (1)

Ben Thul
Ben Thul

Reputation: 32667

Your data is almost in a form that it can be used as is.

create table #d (
    dt datetime not null,
    tz char(3) not null
);

insert into #d (dt, tz)
values 
    ('1/1/2020 10:34 AM', 'EST'),
    ('1/2/2020 08:26 AM', 'CST');

create table #tz (
    tz char(3), 
    long_tz sysname not null
)

insert into #tz (tz, long_tz)
values 
    ('EST', 'Eastern Standard Time'),
    ('CST', 'Central Standard Time');

select #d.*, #d.dt at time zone #tz.long_tz at time zone 'UTC'
from #d
join #tz
    on #d.tz = #tz.tz;

To elaborate on what's going on here, I'm using the at time zone clause twice - once to tell the database what time zone the data is already recorded in and then the second to take that and convert it to UTC.

You can find the list of supported time zones in sys.time_zone_info.

Upvotes: 1

Related Questions