Friday, June 4, 2010

Thou shall not transmit localized DateTime values.

Date and time formats come in a variety of flavours, but one combination causes countless bugs, crashes, and potentially numerous lawsuits: dd/mm/yyyy vs. mm/dd/yyyy. If you ever come across "It worked yesterday, but it's crashing today" pretty much anywhere other than North America you've met this little gem.

The important thing to remember about DateTime values is that they are floating point numbers. 06/01/2010 is NOT a date, it is the localization of a date. The computer you give that localization to has to interpret what the actual date is. Is it January 6th, or June 1st? ANY, and EVERY time you need to transmit or transfer a DateTime from one source to another, you MUST take this into account. Generally this means every time the datetime transfers from one machine to another, such as to a Database server, a reporting server, or a web service. Today that may be two machines that have the same regional settings, but it just takes one server or client PC to be set to a different regional date format to totally hose your system. One thing you should NEVER see in code is a ".ToString()" (empty parameters) applied to a DateTime variable.

Now you don't need to go and start passing DateTime values around as floating point numbers to avoid this issue. (though that is one option.) There is one other practical option when transmitting DateTime values is to use ISO formatting. International Standard formatting for DateTime values is: yyyy-MM-dd HH:mm:ss. That is, 2010-01-06 19:04:30. In .Net this is commonly known as "Sortable". The main reason why this is advantageous is that regardless of their local date formats, any application worth its weight in salt *will* accept an ISO DateTime with its 24hr time format. ISO DateTimes also have the advantage that they are fully sortable so you can sort down to the month, day, hour, etc. or generate sequential numbers for things like filenames.

So there's no need to abandon the DateTime field type whether in .Net, SSRS, Crystal Reports, or SQL Server / Oracle or start farting around with regional settings; Just adopt ISO date and time formats when passing around dates.

No comments:

Post a Comment