American Date format

As a developer, one of the things that piss me right off is the ridiculous default date format used by Microsoft's OSs. When you choose to use the English (US) format for dates and numbers, you get, as the default the following date format: mm/dd/yy (or perhaps you get mm/dd/yyyy which is only marginally better). The international date format is yyyy/mm/dd, and there is an excellent rationale for always using this.

Take today's date: 2008-08-18. In the default setting it would read 08/18/2008. Let's pretend that we are developing a report and need to extract information from a database. If we have the following dates, in the mm/dd/yyyy format and we decide to put it into a character field (for instance, we shove it into a Word document table and the column is set as Text), how would you expect it to sort?

08/18/2008
08/17/2008
08/18/2009
08/19/2008
08/17/2009
08/19/2009


You would think it would sort in proper date order, but you would be wrong. It ends up like this:

08/17/2008
08/17/2009
08/18/2009
08/18/2009
08/19/2008
08/19/2009


Now, you could set it as a Date column, but there are many times you have this in a CSV format and you import into an app that just dumps it into a Text or Char field. Then you end up with this issue. If the dates had been formated correctly: yyyy-mm-dd then it doesn't matter if the data is stored as Text/Char or Date. It would sort correctly no matter what:

2008-08-17
2008-08-18
2008-08-19
2009-08-17
2009-08-18
2009-08-19


There is a much greater problem when the date is stored in the format mm/dd/yy (that is, with only a two digit year). If you get a date that looks like this 04/05/07 how do you know what it is without first knowing the exact format that was used? That could be yy/mm/dd...hell, it could even be dd/mm/yy. An application may not convert the date to its date format correctly at all. If I were to import that into my database, it would become 2004/05/07 and NOT 2007/04/05 as was the intention (mm/dd/yy).

Short dates should be stored as yyyy-mm-dd (I don't care if you use a "-" or a "/", but with a backslash, if you were to use the date in a filename it would give you an error since filenames can't include "/". It means you would have to strip out the "/"s before creating a file. Small thing I know, but still, an extra step is an extra step). This format should be used internationally.

Comments

Anonymous said…
Fortunately most notable databases such as Oracle, MySQL and I believe Postgress output dates as yyyy-mm-dd by default. You can usually override this in your query using a built in function. Unix timestamps (time since epoch) are other common standard.

In fact, most systems do something similar. In my experience the mm/dd/yy format is usually an artifact introduced by a clueless user who exported the data into a csv or similar exchange format. They meant well and converted the data for us from the "ugly" format. :P

Popular Posts