SQL 2016 onwards has a very nice function named AT TIME ZONE.

It lets you convert a UTC time to a timezone defined in the windows registry.

Saves some confusion around an already frustrating part of SQL, date/time handling.

Saves you storing both UTC and local too (e.g Wonderware MES DB).


 

Example table with UTC datetime stamps:


 


 

Example query for expressing this in a Brisbane or NSW\VIC\remainder of people who don’t think that daylight saving makes your curtains fade faster datetime:


 

SELECT

                  log_on_time_UTC AT TIME ZONE 'E. Australia Standard Time'

AS log_on_time_UTC_TimeZoneBris       

                  ,log_on_time_UTC AT TIME ZONE 'AUS Eastern Standard Time'

AS log_on_time_UTC_TimeZoneNSW

  FROM table


 

Registry location: