CODEWITHSUNDEEP
sqlsql-servert-sqltimezone
DC07
DC07

Reputation: 303

SQL - AT TIME ZONE

I have this query that correctly returns the Central European Time as +2 hours. However, it does not add these two hours to the time. How do I add these two hours time (or x hours depending on time zone)? 

DECLARE @targettimezone AS sysname = 'Central European Standard Time'
SELECT convert(datetime2,'2018-10-25T13:43:19.296Z') AT TIME ZONE @targettimezone;

Upvotes: 3

Views: 5468

Answers (3)

Ben Thul
Ben Thul

Reputation: 32707

Let's give the engine some help:

DECLARE @targettimezone AS sysname = 'Central European Standard Time'
SELECT convert(datetime2,'2018-10-25T13:43:19.296Z') 
    AT TIME ZONE 'UTC' 
    AT TIME ZONE @targettimezone;

I'd expect the format that you specified for your timestamp to be interpreted as UTC natively, but it doesn't seem to be. So the above is just explicit about it. ¯\_(ツ)_/¯

Upvotes: 4

tj-cappelletti
tj-cappelletti

Reputation: 1864

You need to capture the offset using the DATEPART function in SQL Server then apply it to your UTC time.

DECLARE @utcDateTime DATETIME = '2018-10-25T13:43:19.296Z'
DECLARE @targetTimeZone AS SYSNAME = 'Central European Standard Time'

DECLARE @offsetMinutes INT
DECLARE @targetDateTime DATETIME

--Get the offset value in minutes
SET @offsetMinutes = DATEPART(tz, CONVERT(DATETIME2, @utcDateTime) AT TIME ZONE 
@targettimezone)
--Add the offset to the UTC time
SET @targetDateTime = DATEADD(MINUTE, @offsetMinutes, @utcDateTime)

--No you have the value in the target time zone
SELECT @targetDateTime

Upvotes: 0

D-Shih
D-Shih

Reputation: 46229

You can try to use datetimeoffset instead of datetime2.

Defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock.

Then convert the datetimeoffset to DateTime can get your expect the result.

DECLARE @targettimezone AS sysname = 'Central European Standard Time'
SELECT cast(cast('2018-10-25T13:43:19.296Z' as datetimeoffset) AT TIME ZONE @targettimezone as datetime2);

sqlfiddle

Upvotes: 4

Related Questions