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.
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: