We’ve all had fun with dates in SQL server, especially if you’ve worked on a brownfield project where someone else has done the installation for SQL server and hasn’t realised that when selecting “English” for the Language option, this actually means “American English” as opposed to “English English”.
Now of course America is pretty much the only country in the world which uses the MM-DD-YYYY format, while we Brits much prefer DD-MM-YYYY (small, bigger, biggest). So when it comes to universal date formats, one thing I didn’t realise is that our chums in America thought that we would still want the day to appear before the month.
As you’ll see in my screenshot below, if you set your language to British over America, and then cast a YYYY-MM-DD as a datetime, it’ll get converted to YYYY-DD-MM.
My screenshot kind of gives away the solution though, as you can see this doesn’t affect datetime2 or date types.
If anyone from America can enlighten me on why datetime handles dates in such a crazy format i’d love to know, as I’ve never seen it used anywhere in Europe.