Calculate Time At Specific Timezone, From Utc Time

I want to calculate Datetime at given timezone based on Datetime in UTC. I thought that I can do it with the following: DECLARE @timeUTC DATETIME = '2019-01-01 10:00:00' SELECT @

Solution 1:

At time zone documentation clearly states:

Converts an inputdate to the corresponding datetimeoffset value in the target time zone. When inputdate is provided without offset information, the function applies the offset of the time zone assuming that inputdate is in the target time zone. If inputdate is provided as a datetimeoffset value, then AT TIME ZONE clause converts it into the target time zone using the time zone conversion rules.

(emphasis mine)

If you'll declare @timeUTC as DateTimeOffset and not as DateTime you'll get different results - also, note that once you've converted the DateTimeOffset back to DateTime you'll get funky results.

Also, please note that the yyyy-mm-dd hh:mm:ss string representation format is a localized format when working with DateTime - that is not the case with the newer DateTime2 data type, which is one more reason why you should never work with DateTime again.

See a demo on DB<>Fiddle

Solution 2:

Here's a trick I use from time to time:

DECLARE @timeUTC DATETIME = '2019-01-01 10:00:00'

SELECT @timeUTC AS timeUTC, @timeUTC AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time' as at_time_zone_offset

Why does this work? Your original datetime has no offset information attached to it (other posters here have explained what the default is when this is the case) The first at time zone clause tells SQL Server "this datetime represents a time in UTC" and outputs a datetimeoffset data type. The second at time zone clause then tells it to convert it to your desired time zone.

Solution 3:

Supplying the input as a datetimeoffset the AT TIME ZONE hint will convert to the input to the target time zone.

The snippet below is a simple example:

DECLARE@Utc DATETIME ='2019-01-01 10:00:00';
DECLARE@UtcOffset datetimeoffset(7) =@Utc;

SELECT@Utc Utc,
    @UtcOffset UtcOffset,
    @UtcOffsetATTIME ZONE 'Central European Standard Time' UtcConverted;

-- Results-- Utc          1/1/2019 10:00:00 AM-- UtcOffset    1/1/2019 10:00:00 AM +00:00-- UtcConverted 1/1/2019 11:00:00 AM +01:00 

Solution 4:

Zohar Peled explained it just fine, but just in case, here is a code example:

DECLARE@timeUTC DATETIME ='2019-01-01 10:00:00';

SELECT@timeUTCAS timeUTC,
    @timeUTCATTIME ZONE 'Central European Standard Time'as at_time_zone_offset,
    CONVERT(datetime, cast (@timeUTCATTIME ZONE 'Central European Standard Time'as datetimeoffset),1) at_timezone_convert,
    CAST(CAST(@timeUTCAS datetimeoffset) ATTIME ZONE 'Central European Standard Time'AS datetime) AS ResultYouNeeded;