Sql-server – How to handle TimeZone properly in SQL Server

There's no quick fix for this, unfortunately. Internationalization of an application should be part of the very first design discussions, as it really goes to the core of a lot of different areas, including date/time comparisons and output formatting.

Anyways, to get on the track of Doing It Right, it's essential to store the time zone information with the time. In other words, realizing that the date/time 20130407 14:50 is meaningless without either (a) including the time zone's then-current UTC offset^{(note 1)}, or (b) ensuring that all logic inserting these values first converts to a certain fixed offset (most likely 0). Without either of those things, two given time values are incomparable, and the data is corrupt. (The latter method is playing with fire^{(note 2)}, by the way; don't do that.)

In SQL Server 2008+, you can store the offset with the time directly by using the datetimeoffset data type. (For completeness, in 2005 and before, I would add a second column to store the then-current UTC offset value (in minutes).)

This makes it easy for a desktop-type application, as these platforms normally have mechanisms to automatically convert a date/time + time zone to a local time and then format for output, all based on the user's regional settings.

For the web, which is an inherently disconnected architecture, even with the back-end data set up properly, it's more complex because you need information about the client to be able to do the conversion and/or formatting. This is usually done via user preference settings (the application converts/formats things before output), or simply showing things with the same fixed format and time zone offset for everyone (which is what the Stack Exchange platform currently does).

You can see how if the back-end data is not set up properly, very quickly it's going to get complicated and hacky. I would not recommend going down any of those paths because you'll just end up with more problems down the line.

Note 1:

A timezone's UTC offset is not fixed: consider daylight savings where a zone's UTC offset varies by plus or minus an hour. Also zones' daylight savings dates vary on a regular basis. So using datetimeoffset (or a composite of local time and UTC offset at that time) results in maximum information recovery.

Note 2:

It's about controlling data inputs. While there's no fool-proof way to validate incoming values, it's better to enforce a simple standard that doesn't involve computations. If a public API expects a data type that includes an offset, that requirement will be clear to the caller.

If that wasn't the case, the caller has to rely on documentation (if they read it), or the computation is done incorrectly, etc. There are fewer failure/bug modes when requiring an offset, in particular for a distributed system (or even just web/database on separate servers as the case here).

Storing the offset anyway kills two birds with one stone; and even if that isn't required now, it makes the possibility available later on if necessary. True it takes up more storage, but I think it's worth the trade-off because the data is lost if it's never recorded in the first place.