I have long felt that the ambiguity between MMDDYYYY and DDMMYYYY, with or without separators, is intolerable and should have been terminated on Day 2 of SQL's arrival in the world - and not just in SQL.
Then I start to wonder what I would prefer; what I would prefer to see. It's not so easy, is it?
In reality, North America (or wherever) should probably be able to have its weird format, though I think it's ill-advised. America probably sees UK/Europe's format in a similar light. And yet, despite the electronic age, dates still get printed on paper or committed to JPG, PDF or other non-changing format, suggesting that this difference should indeed be banished because of its inherent uncertainty or even its capacity for mistakes.
DD MMM, YYYY or MMM DD, YYYY works well visually as a local format but, again, once committed to a non-changing medium could even be in the wrong/unexpected language, making the month hard to understand in other parts of the world and therefore open to mistakes.
Further, if you copy the MMM-style text from a table in PDF or perform OCR on a JPG version, you get dates which are now not sortable (as text), cannot be translated reliably and you may not know the source language of that 'hard copy' anyway. Of course, if all the dates listed are in the first 12 days of the month, in a printed numeric date format, the DDMM.. or MMDD.. format remains unknown, which is completely unacceptable.
So I think YYYY-MM-DD should be the universal favourite because it bypasses all of these problems. It is not the prettiest to read and you may feel the need to do a month translation in your head but it does mean the same thing in every country and in every format (soft or hard) and of course it sorts well as text, and can easily be converted to a date or datetime datatype with no chance of error.
The reality of our job is that we essentially spend our working life translating data from one form to another, one system to another, slicing and dicing it for consumption by various people in various ways in various mediums. Having ANY chance of uncertainty about something as fundamental as a date is surely unacceptable.
The world should probably just get used to YYYY-MM-DD format because that world is now a much smaller place and data in all its forms crosses borders, constantly. And it is supposedly an International Standard - ISO8601.