Let your database applications happily traverse timezones

Sometimes developers come across the situation where a datetime value is stored on each row in a table (for example, the created or last-modified datetime), and then subsequently this datetime value needs to be accessed and processed in some way by a client program. If the client program is such that it may be running in any arbitrary timezone (e.g. web users), it becomes difficult or impossible to be sure about when exactly the underlying row was created or modified. A standardized approach, using the UTC (Coordinated Universal Time), is required.


On the SQL Server side:

• When storing a datetime in SQL Server, always generate the datetime in the SQL stored proc itself (in the INSERT or UPDATE statement); never pass it from the calling client program.
• Always store the datetime as UTC and not local -- use the GETUTCDATE() function in SQL Server, not GETDATE()
• For comparisons of current datetime against the datetime on a given row, again ONLY use the current UTC time generated by the SQL Server and not by the client app. To get the current server UTC time, you can create a simple stored proc that just returns the GETUTCDATE() value, perhaps as an OUTPUT parameter.

On the .NET client side:

• Use DateTime.Compare method to compare the datetime values received from SQL Server
• If you need to convert a UTC time returned by the server into the local time, for display purposes, use the DateTime.ToLocalTime method in your client application. This also automatically factors-in any Daylight Saving time rule in effect.

Observing these simple rules will ensure that the date/time comparisons remain valid and accurate in your database applications, whether they are deployed over local networks or over the Internet across the globe. A positive side-effect is also that if you move your server to a different timezone, the logic to handle UTC datetimes needs no change at all - just make sure the server system clock is set to its local timezone.

Happy coding!

PS: Why is "Coordinated Universal Time" abbreviated to UTC and not to CUT? Check out these links:

http://www.nist.gov/pml/div688/utcnist.cfm

http://en.wikipedia.org/wiki/Utc#Symbol.