Subtract hour using "Singapore Standard Time" timezone-CodePudding

select (convert(datetime ,SWITCHOFFSET(CONVERT(datetimeoffset, '2022-02-11 11:15:00.000'),
   (select current_utc_offset FROM sys.time_zone_info where name ='Singapore Standard Time')
))) CurrentTime

How can I achieve below expected result ?

Current Result:

CurrentTime
-----------------------
2022-02-11 19:15:00.000

Expecting Result:

CurrentTime
-----------------------
2022-02-11 03:15:00.000

PS: I can't use -8 or -08:00 in the script.

CodePudding user response：

The offsets in sys.time_zone_info are those to switch from UTC 00:00 to another timezone.

So for "Singapore Standard Time" that's 08:00

But the sign can be changed if it needs to switch to UTC timezone.

SELECT (CONVERT(DATETIME, 
           SWITCHOFFSET(CONVERT(DATETIME, '2022-02-11 11:15:00.000'),
             (SELECT TRANSLATE(CURRENT_UTC_OFFSET,' -','- ') FROM sys.time_zone_info WHERE name ='Singapore Standard Time')
     ))) AS CurrentTime

Better is to use the AT TIME ZONE syntax :

SELECT
  CONVERT(DATETIME, (CONVERT(DATETIME, '2022-02-11 11:15:00.000')
          AT TIME ZONE 'Singapore Standard Time'
          AT TIME ZONE 'UTC')) AS CurrentTime

Both methods will return the same :

CurrentTime
2022-02-11 03:15:00.000