I think that, sadly, that just makes the problem worse though. People posting on this forum will quite likely use DATETIME, and if they are advised to use 'yyyy-mm-dd' string constants and then they assign to a DATETIME (i.e. using the same date constant style) they will get no error message provided that they are using US-English locale (which I would guess is 99% of testing environments) and thus will be harbouring a dangerous bug.
Hence I think that recommending 'yyyymmdd' is a lot safer for the target audience here.
As this is a SQL Server specific forum, we get almost no one here who is using multiple SQL platforms. That reinforces my thinking that folk are better served by being given advice that is "safe" in deference to "standards compliant".
But ... that said ... you raise an interesting point about moving to new standards compliance. I have no idea how that will happen in the Real World though
"*=" has all but disappeared ... which I suppose proves that it is possible to "change", but the code base using "*=" (in SQL Server, probably NOT also in Oracle!!) was small. The current code base in MS SQL Server is massive, compared to the time when "*=" was commonplace, and thus any change will be very hard (i.e. "costly") to implement
I think Microsoft could do more in this regard - whether via a VERBOSE / STRICT mode that "moaned" about anything not standards compliant, or a more robust approach - e.g. Microsoft could disallow DATETIME in all new installs (by default) and provide an option to enable it with a database-specific setting.
Then Newbies would only use DATE / TIME / DATETIME2 and at least we would not get any new code using DATETIME
I don't know why but when DATE & TIME were added I thought of them as "Long overdue"!! rather than "Standards Compliant" and thus I also thought that the String Constant for DATE being 'yyyy-mm-dd' was an annoyance because of the risk of assignment [in the same, flawed, format] to a DATETIME object.
My organisation is probably?? typical; we are not embracing DATETIME2 because it needs more bytes of storage, and we don't need the greater time accuracy; we have not yet considered splitting our existing DATETIME columns into a pair of DATE & TIME columns because of the huge body of code that would need changing. Maybe we should? DATE (i.e. excluding the TIME part) tests would be easier, and SARGable but we have a huge investment in code that uses/expects DATETIME - my guess is that there are many other organisations in a similar situation.
If anyone has a Magic Bullet please let me know