Geek Answers Handbook

Using AT TIME ZONE to get the current time in the specified time zone

I am trying to use the new AT TIME ZONE syntax in SQL Server 2016 and Azure SQL. I'm just trying to get the current time in London like datetime, given daylight saving time. During the execution of all the commands below, the time in London was 3.27 in the morning.

The first step is to get datetimeoffsetwhat I can successfully do as follows:

DECLARE @dto datetimeoffset
SET @dto = (SELECT GETUTCDATE() AT TIME ZONE 'GMT Standard Time')
SELECT @dto

This returns a value, as I expected:

2016-04-04 02:27:54.0200000 +01:00

Then I want to convert this to datetimewhat my applications expect. I tried three different approaches, none of which give me the result I'm looking for:

SELECT SWITCHOFFSET(@dto,'+00:00')
-- Returns 2016-04-04 01:27:54.0200000 +00:00

SELECT CONVERT(datetime, @dto)
-- Returns 2016-04-04 02:27:54.020

SELECT CONVERT(datetime2, @dto)
-- Returns 2016-04-04 02:27:54.0200000

, - - datetimeoffset / ?

+8
3

:

SELECT GETUTCDATE() AT TIME ZONE 'GMT Standard Time'

GETUTCDATE() datetime, . , MSDN:

inputdate , , , inputdate .

, , UTC, , ( UTC + 1 ).

- UTC datetimeoffset .

SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'GMT Standard Time'

AT TIME ZONE, :

inputdate datetimeoffset, AT TIME ZONE , .

, datetime, , :

SELECT mydatetimefield AT TIME ZONE 'UTC' AT TIME ZONE 'GMT Standard Time'

AT TIME ZONE , UTC, datetimeoffset , .

datetimeoffset, datetime datetime2 , . ( switchoffset .)

, Windows "GMT Standard Time". , , . "GMT Daylight Time" - . - timezone, Windows.

+24

- , , . , datepart (tz) AT TIME ZONE.

datepart(tz,UTC_Date AT TIME ZONE 'Central Standard Time')

select dateadd(MINUTE,datepart(tz,cast('2018-07-02 17:54:41.537' as datetime) AT Time Zone 'Central Standard Time'),'2018-07-02 17:54:41.537') as CentralTime


CentralTime
2018-07-02 12:54:41.537
+1

, , SQL Server / , , ., , datetime , , .. Datetime UTC, ., , datetime, , , /, , > , , . .

+1
source

More articles:


All Articles