Code Endeavor's Blog

Oh the Pain Involved in Using DateTime

It is amazing that something as simple as a primitive data-type can cause so much grief to a developer. This has been the case for me when dealing with properties that are of type DateTime. At first, most developers simply desire to treat a property with this data-type like any other datatype (int, double, string, bool). This ends up working just fine until your data starts to really matter, hopefully it is discovered before code gets pushed to production.

Let me explain. There are 3 classifications for this datatype, all serving a slightly different purpose

  • Date – this is a date that does not care about times. A birthday is a typical use for this, as most systems don’t care to collect the exact time you were born

  • DateTime without timezone – The easiest way to explain this one is that it is a property that is collected that is a Date + Time no matter what time zone. New Years works like this one. Each time zone celebrates the new year at exactly the same time (1/1/2013 – 00:00). So people in CST timezone mark this DateTime exactly one hour after people in the EST timezone.

  • Fixed point in time (or DateTime with TimeZone) – This is by far the most common usage for DateTime. Simply put it is a marking of a point in time experienced by all people regardless of the timezone they reside.

Hopefully these classifications don’t come as a surprise to anyone. As to why they cause so much pain becomes evident when you start developing with them, especially when using Microsoft technologies like .NET and SQL Server.

For a long time, .NET and SQL Server were only able to offer a datatype that fulfilled the DateTime without timezone classification, as they did not have the ability to store the timezone that the datatype contained. Sure, there was always the ability to convert from the “local” datetime to “universal” but the logic used to determine the offset was simply the locale the code was running under.

Let’s look at a simple example

  • Flight takes off from Chicago on 1/1/2013 at 9:00AM CST (1/1/2013 3:00PM UTC)
  • Flight lands in Los Angeles on 1/1/2013 at 11:00AM PST (1/1/2013 7:00PM UTC)

Typically you would store the datetimes in UTC allowing for logic to easily be performed to determine how long the flight was (4 hours). However, if this is all you stored, you would not be able to translate the DateTime into the proper timezone without some logic to lookup the offset for the location.

In other words, the .NET datatype of DateTime and the SQL Server datatype of datetime do not have enough “precision” to store the offset. To display the date back to the user you would need the DateTime + location offset.

With .NET 3.5 and SQL Server 2008 Microsoft introduced a new datatype of DateTimeOffset, which is defined as “a point in time, typically expressed as a date and time of day, relative to Coordinated Universal Time (UTC)”. This is exactly what we want to handle our third classification of a date. If you are fortunate to be working on a codebase that can specify these technologies as a minimal you have a bit less to worry about than those of us who are working on older technologies.

However, you may not be “out of the woods” yet. The current best way to transmit data to and from systems is JSON. There is a reason for this: “It just works”. Yes it is easy on the web side of things to use as javascript is understood by all browsers. But even without the web side of things it is an easy way to send object-graphs between systems, and it is human-readable. Unfortunately for JSON there was no specification as to how to send dates across the wire, and many different frameworks decided to roll their own implementations. I honestly, don’t care what format things get passed, only that it doesn’t fall into the same trap as the original DateTime where we lose the timezone. This is exactly what happened, until recently when the developer community is starting to settle on the ISO8601 format. This format has a few variations, but mainly it allows for the timezone to be included


Unfortunately, the Microsoft technologies are not able to accommodate this type of serialized date. Fortunately, they are starting to allow their technologies to be pluggable and other members of the community are stepping up and filling in the gaps of data-serialization where Microsoft has failed for many years. Specifically, in this case James Newton-King has blessed the developer world with a product (JSON.NET) that not only out-performs every serialization library Microsoft has come up with, but elegantly handles datatypes that are tricky like our DateTime/DateTimeOffset datatypes (see IsoDateTimeConverter).

I do wish Mr. Newton-King would come up with a serialization library to replace the Microsoft XmlSerializer as it still cannot handle the DateTimeOffset datatype.

The DateTimeOffset type was not designed to be used with the XmlSerializer. The XmlSerializer requires that a type be designed in a specific way in order to serialize completely (default public constructor, public read\write members, etc). Most types in the .NET Framework were not designed with the XmlSerializer in mind.

It is funny, how when a developer does not approach a technology solution with the idea that he/she can change the underlying framework code, but rather works around it, not only does a better solution come about, but one that is also flexible enough to handle future changes.