Convert Time Between Time Zones in SQL Server

Step 1

Get a list of Time Zones

SELECT * FROM sys.time_zone_info

Step 2

Run a Conversion

SELECT GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'AUS Eastern Standard Time';

The above script essentially tells SQL Server that the datetime is UTC and you want it as Australian time.

This approach is needed when the date time includes no time zone info

The text you enter for the time zone name needs to match the entry in the sys.time_zone_info table.

Pauls Blog