Converting UTC to Local Time in SQL Server

For Salesforce UTC Date Fields

Salesforce stores all datetime fields in UTC (Coordinated Universal Time)  and converts them to the users’ local time when they’re displayed in the UI. If you’re extracting date data from Salesforce via the APIs then they’ll also come across as stored – so in UTC. If you want them to be in local time you need to convert them back. Fortunately this straightforward in SQL Server.

SQL Server provides the “at time zone”  conversion to switch dates between time zones. Given a datetime in UTC you can convert it to any time zone as follows. For example from UTC to GMT.

select	GETDATE() time_in_GMT
, 	GETUTCDATE() time_in_UTC
, 	GETUTCDATE() at time zone 'UTC' at time zone 'GMT Standard Time' UTC_converted_to_GMT

The first at time zone clause converts the datetime to a datetimeoffset type (essentially a time zone aware type) in UTC and the second at time zone converts it to GMT. The datatimeoffset type shows the datetime and the offset for this time from UTC.

Convert UTC date times to GMT data times with at time zone in SQL Server

Generally datetimeoffset types can be treated exactly like a datetime type but if you need to you can cast it back to a datetime:

, cast(GETUTCDATE() at time zone 'UTC' at time zone 'GMT Standard Time' as datetime)

You can find a list of valid time zones on SQL Server in:

select * from sys.time_zone_info

This includes the current offset from UTC for the zone and whether Daylight Saving (DLS) is currently being applied. Note that Greenwich Standard Time does not include DST adjustments (and hence is always the same as UTC) but GMT Standard Time does:

All supported time zones in a SQL Server instance