T-SQL date time formatting in English (UK)

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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s