SQL - AT TIME ZONE

Question

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;

Answer 1

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

Answer 2

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