Managing dates in .net

Recently I faced an issue where I had to move an asp.net application with its data (Sql Server 2008) to a server that is located in a different country. I came across with two problems, both of them related to dates.

• The first issue was that I exported some data from the Sql Server, using a very handy function ds.WriteToXml(). This function adds by default in all date fields a time offset which is the difference of your current timezone to the UTC.
• The second issue was that I was saving all of my date information to UTC. The basic reason was that I had to make some calculations with my date data and I decided to save in UTC to avoid daylight saving issues. But when moving to a server in a different country, the dates that were saved in UTC were presented in the local time of the server (which was England) and not in the client’s local time (which was Greece).

Both issues were difficult to deal at the time of .NET 2.0. After that, the framework provided functions and structures that resolved such issues in a very straight forward way.

For the first issue, you need to know that every DataColumn field has a DateTimeMode property. This property can have specific values (Local, Unspecified, Unspecified Local, UTC). The default value is Local, so at the export of a dataset to an XML file, each date value had a +X offset (X=2 in my case, because the export occurred by a web server in Greece). When this XML file was used to load the data to a database in England, all date values were subtracted by X hours. I would expect that since the datetime field in Sql Server 2008 is timezone unaware that the default value would be Unspecified. In order to solve this, I wrote a simple for each loop in which I traversed all the columns of my DataTable and set for date columns the DateTimeMode to Unspecified. This simple tweak made my LoadXMLtoDb function to work like a charm. You can view about this case here.

For Each _Column As DataColumn In ds.Tables(0).Columns
    If (_Column.DataType Is System.Type.GetType("System.DateTime")) Then
        _Column.DateTimeMode = DataSetDateTime.Unspecified
    End If
Next

The second issue was the use of myDate.ToLocalTime(). I used this function to get the local time from my UTC values so that I will present them to the user. Moving the application to a web server in a different country, this function didn’t return the value I wanted. At first, I tried to set the thread’s culture to “el-GR” (following the instructions here) but it didn’t work for me. The solution I was looking for came by using the TimeZoneInfo namespace. Through that I could convert a UTC datetime to a specific timezone, and vice versa. The TimeZoneInfo namespace is simple to use:

Function ConvertToLocalTime(ByVal myUTCDate As DateTime, & _
                            ByVal tzID As String) As DateTime
    Dim myTzInfo As TimeZoneInfo = TimeZoneInfo.FindSystemTimeZoneById(tzID)
 
    Return TimeZoneInfo.ConvertTimeFromUtc(myUTCDate, myTzInfo)
End Function
 
Function ConvertToUTC(ByVal myLocalDate As DateTime, & _
                      ByVal tzID As String) As DateTime
    Dim myTzInfo As TimeZoneInfo = TimeZoneInfo.FindSystemTimeZoneById(tzID)
 
    Return TimeZoneInfo.ConvertTimeToUtc(myLocalDate, myTzInfo)
End Function

Also, in the TimeZone namespace you can get an array with all the available time zones. This information is built in the .NET framework and it is updated with Windows Update. So, in your project you can easily use this array instead of maintaining a table of your own.

TimeZonesDropDownList.DataSource = TimeZoneInfo.GetSystemTimeZones()
TimeZonesDropDownList.DataValueField = "id"
TimeZonesDropDownList.DataTextField = "DisplayName"
TimeZonesDropDownList.DataBind()

Issues regarding locality and dates are met more often by web developers where the web server, database server and client can be located in places with different locale and timezone. Personally, I deployed a web application that its web server was in England, the database server in USA and all the visitors where from Greece. The application had a simple log in and had to keep track of all visits and how long each visitor stayed in the home page to watch a live streaming. Not the deployment scenario I chose, but due to some problems I ended up with this setup. So it is important that such issues to be addressed in a universal way. In two lines my golden rules are:

• Always store your date data in UTC. UTC doesn’t have daylight saving changes and it is universal. We can use it as base reference and convert the date to our desired timezone easily.
• The use of the DateTime.ToLocalTime() is useful only in scenarios where you know that your web server is in a specific country. But when you have a product (e.g. a CMS) that it deploys in many servers, or you have an SaaS model (which could mean that you have visitors/customers from every corner of the world) you need to make all date conversion using timezone objects.

These two rules are simple to follow, but people might get easily confused with such issues. The .NET framework provides the required tools to handle such issues efficiently. The TimeZoneInfo namespace provides an array with all the time zones that are registered to the web server. This array gets updated from Windows Update. It is very easy to load the array to a DropDownList and let the user decide its timezone.