SQL - AT TIME ZONE

user_7195482 0
Viewed 4387

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;
timezone tsql sql-server sql
3 Answers

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

user_51760710

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. ¯\_(ツ)_/¯

user_5682090

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
user_3292180
Related