Philip van Gass
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]
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]
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
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
*/
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks.
DECLARE @todaysdate datetime = GETUTCDATE()
But will have a look at your query and get back to you