An issue that always seems to plague developers who write software that is used in multiple geographically disperse locations is that of dates and time zones. If a user in the Eastern time zone updates a record that contains a date and time field (say, 1/15/2014 4:35 PM), and then a user in the Pacific time zone views that record, what date and time are they supposed to see? The time as it was entered in Eastern time, or should they see their local time instead (1/15/2014 1:35 PM)?
Generally speaking (there are, of course, exceptions) date and time information should be stored in a database as a UTC (Coordinated Universal Time)
time, and automatically converted and displayed in the user's current time zone in the client application. But that is often not as easy at it seems to implement. Below, I'll outline a process that has worked well for me in several apps.
Step 1 - Getting data from the client to the server
Sun Feb 23 2014 15:33:09 GMT-0400 (Atlantic Standard Time)
This would seem to contain all the information we require, but alas, the default ASP.NET WebAPI configuration can not translate this to a C# DateTime instance. But there is a solution - the ISO-8601 date format
, which looks like this:
In fact, most modern browsers have the ability to output this format using the toISOString() method, but there are a couple of drawbacks. First, the toISOString() method is not supported on all browsers and second, this built-in method deletes the time zone information and simply outputs it as UTC (such as 2014-02-23T19:33:09Z). Sometimes it is desirable to preserve the originating time zone data.
Thus, I prefer to use the excellent moment.js library
Step 2 - Processing on the Server
Actually, with WebAPI and its default use of Newtonsoft's Json.NET library, there is nothing else you have to do on the server. Using the default model binder, any date that is submitted in ISO-8601 format will be automatically converted to local server time and, most importantly, the Kind property of the DateTime instance will be set properly to DateTimeKind.Local.
Step 3 - Saving in SQL Server
Now here's one step I see a lot of people miss - saving the datetime properly in SQL Server. We want the date to be stored as a UTC value so that it can be converted to any time zone for display purposes. In order to do this, you must convert the C# datetime instance to UTC when adding it to the parameters of your stored procedure, like so:
Step 4 - Retrieving from SQL Server
Another step often missed by developers is the requirement to properly stamp the Kind property of the C# DateTime instance when retrieving it from the database. You must mark the value as UTC like this:
myDateField = DateTime.SpecifyKind((DateTime)dbReader("myDateField"), DateTimeKind.Utc);
Step 5 - Displaying on the Web Page
By following this process you can ensure that all date and time data in your database is consistently represented as UTC, and can be reliably converted to the time zone being used by the client application. One gotcha to be careful of, however, is when filtering date values. Just like storing a date value in the database, the client specified date filter must be converted to UTC time before the comparison against SQL data is performed.