Being sick of the difference between date formats in different systems, I decided to always format my dates in YYYY-MM-DD, Nobody’s going to get that screwed up, are they?

I ran a query in an enterprise system today to retrieve everything since ‘2014-11-03’, it didn’t work???
It turns out that YYYY-MM-DD does not have universal consistency, some fools decided that YYYY-DD-MM was a good idea, I mean, who uses that!! 

So I went looking for a new standard and found…

ISO 8601 – ‘Data elements and interchange formats – Information interchange – Representation of dates and times’ http://en.wikipedia.org/wiki/ISO_8601
At first glance it looks like I was already using that (YYYY-MM-DD), but as it turns out, SQL Server didn’t know I was.

So how do I tell SQL Server to use ISO 8601 easily?
When converting just a date, remove the formatting

SELECT CAST('2014-11-03' AS DATETIME) -- Bad date
SELECT CAST('20141103' AS DATETIME) -- ISO 8601

There is a catch however, when adding a time, they must be formatted, but add a T between the date and the time

SELECT CAST('2014-11-03T16:30:28.000' AS DATETIME) -- ISO 8601
SELECT CAST('2014-11-03 16:30:28.000' AS DATETIME) -- Bad date (and waste of time) 

Note that you will need to use SET DATEFORMAT YDM to test these.

So in conclusion, if you want a hot date, always talk using ISO 8601, but be careful, if you get it wrong, there will be a conversion failure, then nobody will know who’s talking about what.

P.S. If you actually talk about ISO 8601 (or any other standards) on a date then it’s highly unlikely to be a hot date.