I read with interest Matt Masson's latest blog entry "Optional date parameter". Its a useful technique and thus well worth adding to your SSIS armoury.
It did raise a wider issue though. For demo purposes Matt was using a date of "12/30/1899". Now some people, typically those in North America, would interpret that as 30th December, 1899 whereas others (including myself) would interpret it as the 12th day of a non existent 30th month in the year 1899. See the problem?
Ambiguous date formats are a pet hate of mine and I'm not just talking about within SSIS, I'm talking about every day life. I've been living in California for the past 10 months or so and I quickly lost count of the number of times that I entered a date in the format I am used to and then scribbled it out to write it in the (locally) correct US format. I'm not laying the blame at anyone's door, its just the way things are.
As IT professionals though, particularly data professionals, we have to be stricter about how we represent dates. In other words, they need to be unambiguous. The only real accepted technique for representating dates (and times) in an unambiguous format is to write them in decreasing unit order. For example:
"18991230 23:45:56"
OK, the use of seperators may differ from place to place but there is no doubting that the above date refers to eleven forty five and fifty six seconds on the evening of 30th December, 1899. Using unambiguous date formats is a good habit to get into.
Those of you who have read my blog before might know that I work predominantly with SQL Server and as such I wrote this entry two years ago that discusses how to deal with dates in SQL Server.
-Jamie