Link to home
Start Free TrialLog in
Avatar of Philip van Gass
Philip van GassFlag for South Africa

asked on

The SWITCHOFFSET function in T-SQL

I am getting an unexpected result when I apply the SWITCHOFFSET function to a date.
The following query is giving me a time for 'Central European Standard Time' which is 2 hours behind UTC time.
I expected it to be 2 hours ahead. Can you also try it and see what you get.
Am I understanding the time zones incorrectly or the SWITCHOFFSET function incorrectly ?

DECLARE @todaysdate datetime = GETDATE()

SELECT  @todaysdate,
    @todaysdate AT TIME ZONE 'Pacific Standard Time' AS Date_TimeZonePST,  
      @todaysdate AT TIME ZONE 'UTC' AS Date_TimeZoneUTC,
       @todaysdate AT TIME ZONE 'Central European Standard Time' AS Date_TimeZoneCET,
    SWITCHOFFSET (@todaysdate AT TIME ZONE 'UTC', 0) as [UTC],
      SWITCHOFFSET (@todaysdate AT TIME ZONE 'Central European Standard Time', 0) as [CET]
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Should be using

DECLARE @todaysdate datetime = GETUTCDATE()

But will have a look at your query and get back to you
Yep, the offset is relative to the base date, so you have to get the correct starting point.

I think you will find that the -2 is telling you the offset from CET is UTC time is two hours behind. Not that CET is +2 hours - does that make sense ?

For me, my time zone is +10, so the function based on GETDATE() is out by +10

DECLARE @todaysdate datetime = GETDATE()

SELECT  @todaysdate, 
    @todaysdate AT TIME ZONE 'Pacific Standard Time' AS Date_TimeZonePST,  
      @todaysdate AT TIME ZONE 'UTC' AS Date_TimeZoneUTC,
       @todaysdate AT TIME ZONE 'Central European Standard Time' AS Date_TimeZoneCET, 
    SWITCHOFFSET (@todaysdate AT TIME ZONE 'UTC', 0) as [UTC],
      SWITCHOFFSET (@todaysdate AT TIME ZONE 'Central European Standard Time', 0) as [CET]
;
go
/*
(No column name)	Date_TimeZonePST	Date_TimeZoneUTC	Date_TimeZoneCET	UTC	CET
2018-07-16 10:26:59.060	2018-07-16 10:26:59.060 -07:00	2018-07-16 10:26:59.060 +00:00	2018-07-16 10:26:59.060 +02:00	2018-07-16 10:26:59.060 +00:00	2018-07-16 08:26:59.060 +00:00
*/


DECLARE @todaysdate datetime = GETUTCDATE()

SELECT  @todaysdate, 
    @todaysdate AT TIME ZONE 'Pacific Standard Time' AS Date_TimeZonePST,  
      @todaysdate AT TIME ZONE 'UTC' AS Date_TimeZoneUTC,
       @todaysdate AT TIME ZONE 'Central European Standard Time' AS Date_TimeZoneCET, 
    SWITCHOFFSET (@todaysdate AT TIME ZONE 'UTC', 0) as [UTC],
      SWITCHOFFSET (@todaysdate AT TIME ZONE 'Central European Standard Time', 0) as [CET]
;
go
/*
(No column name)	Date_TimeZonePST	Date_TimeZoneUTC	Date_TimeZoneCET	UTC	CET
2018-07-16 00:26:59.163	2018-07-16 00:26:59.163 -07:00	2018-07-16 00:26:59.163 +00:00	2018-07-16 00:26:59.163 +02:00	2018-07-16 00:26:59.163 +00:00	2018-07-15 22:26:59.163 +00:00
*/



DECLARE @todaysdate datetime = SYSUTCDATETIME()

SELECT  @todaysdate, 
    @todaysdate AT TIME ZONE 'Pacific Standard Time' AS Date_TimeZonePST,  
      @todaysdate AT TIME ZONE 'UTC' AS Date_TimeZoneUTC,
       @todaysdate AT TIME ZONE 'Central European Standard Time' AS Date_TimeZoneCET, 
    SWITCHOFFSET (@todaysdate AT TIME ZONE 'UTC', 0) as [UTC],
      SWITCHOFFSET (@todaysdate AT TIME ZONE 'Central European Standard Time', 0) as [CET]
;
go
/*
(No column name)	Date_TimeZonePST	Date_TimeZoneUTC	Date_TimeZoneCET	UTC	CET
2018-07-16 00:26:59.190	2018-07-16 00:26:59.190 -07:00	2018-07-16 00:26:59.190 +00:00	2018-07-16 00:26:59.190 +02:00	2018-07-16 00:26:59.190 +00:00	2018-07-15 22:26:59.190 +00:00
*/

Open in new window

Avatar of Philip van Gass

ASKER

Thanks Mark. So if you specify a time zone of e.g. 'Central European Time', the SWITCHOFFSET assumes that you, the user, are located in that time zone and it gives you what the UTC time is relative to it.
ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks.