In Praise of ISO 8601
As a UK ex-patriate and US resident I'm very sensitive to date formats. Moving from the DD/MM/YYY format to the MM/DD/YYYY format was a traumatic experience, and after eight years I still get a nervous twitch when I need to place the month before the day. What kind of a twisted mind thought that up?
Probably the same one who decided that in my first job in the US I'd be dealing with a system for which the preferred date display formats were to be YYDDD and YYYYDDD. There can't be many people who are instinctively aware that the first of October is day 274 of a non-leap year, but if you find someone who is then I can tell you who they work for. Furthermore, they may belive that this data format is known as the Julian Date, and they're wrong about that of course. Further-furthermore it's surprising how many database designers believe that a requirement to display a date in YYYYDDD format means that it is a great idea to store it as a NUMBER(7), leading to it being displayed by default in many tools as "2,006,274.00". Actually it's not surprising, it's just irritating for the hopefully-obvious reasons.
The other exception to the adherence to the MM/DD/YYYY format was the Immigration Service, who seemed to prefer DD/MM/YYYY on their forms. Thus I was lulled into a false sense of security through the whole visa process, and only ambushed with the MM/DD/YYYY abomination when I was fully commited to the process.
This is why I have such a liking for ISO 8601, which specifies the standard date format to be YYYY-MM-DD, with a reduced precision option of YYYY-MM and an optional time component of what we Oracleers would represent as hh24:mi:ss. I could lay out a complete set of reasons why this makes more sense but it is done very well here, and the display of week numbers is also nicely handled.
Now then, do you suppose that if the massed hordes of database developers and administrators rose up against the MM/DD/YYYY and the less-objectionable DD/MM/YYYY and DD-Mon-YYYY formats in favour of YYYY-MM-DD then we might make a meaningful impact on this scourge? I'd hope so. If we all started a "Movement Against Date Display Stupidity" (MADDS) by claiming that there was a fatal bug in the system when displaying anything other than YYYY-MM-DD then we might deceive our way to virtue, and all mankind would benefit.
It'd do me a power of good, anyway.
posted by David Aldridge @ 7:00 AM
12 Comments:
I worked for a European brach of a US company. Since data entry was our problem, we used DDMMYY (this was pre Y2K - and in one old system it was DDMMY - saved a byte!). Reports might end up anywhere, including the USA, so dates were printed & displayed as MON DD, YYYY.
I'm firmly of the opinion that the month in letters is the only way to go for display.
ugh ... to hear a multilingual international citizen like yourself say that ... it breaks my heart it does.
I've seen TO_DATE(some_English_literal,'DD-MON-YYYY') work just fine for several months, then suddenly fail in the Paris office. It took everyone a while to realise that there is no such month as 'FEB' if you're French. (The Munich office would have lasted a bit longer.) I'm with David.
Just an opinion:
I always write (on paper) the dates in format DD-MM-YYYY (which is normal here in Spain) but I use roman numbers for the months: 14-IV-2006. This removes all the problems of taking the days for months and vice-versa.
I've often thought this could be a usefull format to implement in Oracle (say DD-Mr-YYYY or something similar...).
Cheers
Carlos.
SQL> select to_char(sysdate,'DD-fmRM-YYYY') from dual;
TO_CHAR(SYSD
------------
14-IV-2006
1 row selected.
Ingenious but perverse :)
SQL> select to_char(sysdate,'DD-fmRM-YYYY') from dual;
Wow! It is 10 years since I work with Oracle and it is the first time I see this format.
Everyday I learn something, they say.
Thanks & Cheers.
Carlos.
Carlos, you crazy crazy man. Don't you even think of using roman numerals for months in a system. And William, stop encouraging him.
Obviously great that literal dates can now be represented in oracle SQL and PL/SQL as date'2006-04-18' which is using this ISO standard. It is much better than the to_date('18-apr-2006','dd-mon-yyyy') you see everywhere.
Yes, absolutely. It's a shame that abbreviated and other ISO formats aren't supported, such as ...
date '2006'
date '2006-04'
date '2006-04-18 23'
date '2006-W03'
date '2006-04-18 23:15'
date '2006-04-18 23:15:16'
Yeah, we'll get it done just after we finish that metric conversion.
Julian Dates.
Yay for metric!
I learned a new word from that link btw, "nychthemeron". now, I just need to slip it into a conversation all casual like ...
Perhaps oddly, you can have a TIMESTAMP literal:
SQL> SELECT TIMESTAMP '2006-04-18 23:15:16' FROM dual;
TIMESTAMP'2006-04-1823:15:16'
---------------------------------------------------------------------------
18-APR-06 23.15.16.000000000
1 row selected.
which you could cast into a DATE:
SQL> SELECT CAST(TIMESTAMP '2006-04-18 23:15:16' AS DATE) FROM dual;
CAST(TIMEST
-----------
18-APR-2006
1 row selected.
...though for that much effort you might as well have stayed with TO_DATE().
What I want to know is, where is the date mask that tells me it's the Ides of March?
Post a Comment
<< Home