Localizing Dates In Sql Server
Several months ago I added timezone handling to a CF app. This post is my best effort to document how to localize dates using MSSQL timezone functionality.
Retrieving Timezones from MSSQL
First, we need to know what time zones we can use. MSSQL uses the Windows timezones as stored in the registry, and they can be retrieved from the
sys.time_zone_info system view.
SELECT * FROM sys.time_zone_info
Do not try to interact with these time zones using a CFML timezone - the underlying Java timezones use a different timezone format and do not match the MSSQL/Windows timezone format.
Also, you may notice that the timezone list does not include different timezones for daylight saving time. For example, there is a single timezone record for
Eastern Standard Time, or EST.
Eastern Daylight Time is nowhere to be found, because this view automatically updates the
is_currently_dst values based on the current time in that timezone.
Retrieve Current UTC Offset From a Known Timezone
Next we use these time zones to get the current utc offset. Note I said current - do not store the UTC offset, because it will change for a given time zone twice a year at minimum. (e.g. daylight saving time.)
For this reason, we'll need to store the user's time zone and retrieve the UTC offset when you need the user's local time.
Here's a simple way to retrieve the current UTC offset for a known time zone:
SELECT current_utc_offset FROM sys.time_zone_info WHERE name='US Eastern Standard Time'
Obviously, in order to know which time zone to use you will need to allow the user to select their time zone so you can store that timezone with the user.
Localize Dates To a Timezone Using AT TIME ZONE
AT TIME ZONE if you need to indicate the UTC offset of a datetime value:
GETDATE() AT TIME ZONE 'US Eastern Standard Time'
Basically, this lets us select a date which was stored with no UTC offset and add a UTC offset to it - provided we know the original time zone. This paves the way for us to then convert the date from its original timezone - using
AT TIME ZONE - to another timezone - using the
Once we have a
DATETIMEOFFSET value, we can use
SWITCHOFFSET(DATETIMEOFFSET, time_zone) to switch that datetime to match a user's local time:
SWITCHOFFSET( GETDATE() AT TIME ZONE 'US Eastern Standard Time', ( SELECT current_utc_offset FROM sys.time_zone_info WHERE name='Central European Standard Time' ) )
Unfortunately, this is not a full timezone localization writeup. Localization is very complex - especially dates and timezones - and the more I learn the less I know. But hopefully this brief overview of timezone handling using built-in MSSQL functionality will come in useful for someone - enjoy!