>> Actually, the format YYYYMMDD is perfectly acceptable under ISO. I don't know why Celko insists on repeating his false claim over and over about YYYY-MM-DD despite being told that it's false.<<
The reason I insist on this is that I was serving on the ANSI standards committee when we put it in the language 🙂 The "YYYY-MM-DD" was picked from several options in the ISO 8601 standard for the following reasons:
1) it is an ISO standard and X3H2 has always supported ISO standards. We don't like local dialects.
2) these display strings sort correctly in temporal order. They are not ambiguous.
3) They are not language dependent (I really don't like Oracle's default format for that reason).
4) these display strings cannot be confused with numerics because of the dashes. This is a principal reason we rejected the "YYYYMMDD" option. Why do you think it is not ambigous?
5) The regular expression for it is very simple.
6) by allowing one and only one ANSI/ISO standard display format, we make parsing and storing the data much easier without having to worry about support for dozens of national formats, ordinal date format, week within year format, etc.
The principal architect of the temporal extensions was Rick Snodgrass, at the University of Arizona. This was his area of academic specialization and he put out a book on temporal queries and SQL is available as a PDF download from the University of Arizona. It is truly a classic and should be on the bookshelf of every working SQL programmer.
I found that when you build a calendar table it's very handy to have the ISO 8601 ordinal date and the ISO day-within-a-week format. The second format is very popular in the Nordic countries.
My personal opinion is that we should have required the "T" separator between the date and the time parts of a timestamp. This would make a timestamp a continuous string without any worries about embedded white space like we have now.
Please post DDL and follow ANSI/ISO standards when asking for help.