I’m working on a .NET app that handles time sensitive date entry from a number of locations. I hate to admit it but this is really the first I’ve seriously had to deal with DateTime values coming from multiple locations and keeping everything synched up properly so that everybody sees the time properly adjusted for their location. This goes both for the Windows Forms app, the ASP.NET front end and the SmartPhone app that all will be used to access this application.

I’ve used Universal Time values for storage before – I try to remember <bg> to this with all developer tools that need to store timestamps to make sure there aren’t any problems translating values. However, in most of those situations the usage has been pretty simple as the data has been meta data that’s only used by the system.

So, first timing it (and yes any tips are welcome <g>) here’s what I am doing so far.

All data stored into the database is stored in UTC format. The first and most obvious place to handle conversions is in my business object at the Entity level where the Save method automatically converts the time to UTC before the data gets saved. On the way out from the database the Load() method of the Entity does the reverse of turning the UTC time back to the current local time.

So far so good, this actually handles the most common scenario and it’s a do once and forget sort of thing. As long as I’m using my business object entity I’m fine.

My concern though is with database queries. Having the data in UTC format means that every query against the data base – SELECT, INSERT, UPDATE, Stored Procs – also needs to send its dates in UTC format since that’s how the data is stored in the backend this way. So every DateTime value needs to have a .ToUniversalTime() conversion. While not difficult this is an implementation detail that’s easy to forget.

Maybe the more prominent problem is that when data gets returned from the server in a query I need to convert that as well. So if I pull back a data table I need to go through the DateTime data and translate the data by running through the data table rows and changing each of the values. This can be done in the business object, but using a plain DataReader for retrieving data is out in this scenario, unless I’m missing something, since there’s no real way to hook the reader’s output stream.

Yuk… The other option – even worse I suppose – is to do the conversion in the front end at the databinding end. Nah – can be done, but this is really not right.

So any hints on how to deal with this elegantly? Really, the logic of this is not difficult, but I would like to find a way to isolate this conversion business as much possible – at the very least all in the business object layer (which should be doable in most cases).