Converting Time Zone in Queries

How can I export dates and times in another timezone? 🤔

Converting between time zones in Slate is a two- or three-step process.

1. Attach Eastern Standard Time to a raw datetime value.
   
2. Convert the newly-zoned datetime value to the target time zone.
3. (Optionally) format the value for display.

@val at time zone 'Eastern Standard Time' at time zone '<target time zone>'

Example

Let's convert Person Created Date from the default (Eastern Standard Time) to Mountain Standard Time. This query contains the Created Date export twice. The first one is unmodified. The second has Format Type set to Formula / Custom SQL and this formula: @val at time zone 'Eastern Standard Time' at time zone 'Mountain Standard Time'

Results:

Notice that the converted values have a time zone attached, unlike the raw values. Next, let's format the output for display. Change the custom format mask to format(@val at time zone 'Eastern Standard Time' at time zone 'Mountain Standard Time', 'g') or your favorite format string, like 'F'. For this example, I also set the "Person Created Date" export to Format Mask g.

Note the resulting syntax:

select 
    format(p__JID_.[created], 'g') as [Person Created Date], 
    format(p__JID_.[created] at time zone 'Eastern Standard Time' at time zone 'Mountain Standard Time', 'g') as [Person Created Date MST]
from [person] p__JID_

Results:

You cannot use the "easy" Format Type Date/Time + Format Mask because it results in invalid syntax like select format(p__JID_.[created], 'at time zone ''Eastern Standard Time''') as [Person Created Date]. The clause at time zone must not be in quotes.

Selecting the Time Zone

What if you don't know the name or exact spelling of the target time zone? Some of them are abbreviated or otherwise hard to guess.

Search online for the city, such as Beirut, to find the time zone, offset, and whether DST is observed. Then search the database list of time zones to find the exact spelling.

SELECT *
FROM sys.time_zone_info
WHERE current_utc_offset = '+03:00'
    AND is_currently_dst = 1

References

• AT TIME ZONE