CODEWITHSUNDEEP
sqlazureazure-synapsesql-data-warehouse
Erik Shilts
Erik Shilts

Reputation: 4509

Convert timezone in Azure SQL Data Warehouse

I have a DATETIME column from the US/Pacific timezone, but it is not encoded as such. How can I convert this to UTC timezone in Azure SQL Data Warehouse?

The AT DATETIME T-SQL function seems like the closest fit, but it is not supported by Azure SQL Data Warehouse. https://learn.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-transact-sql

For example, one record has a DATETIME of 2013-04-02 08:02:47.000000. After conversion it should be 2013-04-02 15:02:47.000000.

Upvotes: 2

Views: 1762

Answers (3)

Erik Shilts
Erik Shilts

Reputation: 4509

Because my data were stored in 'US/Pacific' I used TODATETIMEOFFSET() to add the specific offset to the data. Once stored as a DATETIMEOFFSET type, it is treated as UTC time by the server but the timezone offset is still available.

SELECT TODATETIMEOFFSET(time_in_pt, '-08:00') as time_with_pt_timezone ...

https://learn.microsoft.com/en-us/sql/t-sql/functions/todatetimeoffset-transact-sql

Upvotes: 2

Murshed_MSFT
Murshed_MSFT

Reputation: 76

Right now (in winter) we are in PST which is UTC – 8 hours. So converting your date 2013-04-02 08:02:47.000000 to UTC will be 2013-04-02 16:02:47.0000000 value.

declare @mydate datetime2 = '2013-04-02 08:02:47.000000' select dateadd(hh, 8, @mydate) as utcdate

Upvotes: 0

snickholas
snickholas

Reputation: 51

it's a little hard to answer with no context, but i believe you could just cast or convert the column to whatever date/time type you desire. Accounting for the timezone is hard to say, yet again, with a lack of context.

Upvotes: 0

Related Questions