First, a little bit of history about why I am writing on this specific topic:
If you hang around any of the SQL forums, you will see that a lot of issues that people face with dates are related to confusion about how to use date literals. Should you use ‘7/18/2013′ or should you use ’18/7/2013’? What if I am in the United States and my system language is British English? Which of these two if any should I be using?
Pretty much anyone who is fairly familiar with SQL knows that to avoid these kinds of problems, one should always use date literals in the ISO-8601 format. There are two or 3 flavors of ISO-8601 format.
--- YYYYMMDD format. SELECT CAST('20130718' AS DATE); --- YYYY-MM-DDTHH:MM:SS format. SELECT CAST('2013-07-18T17:12:14' AS DATETIME)
There are others, you can see them for yourself here http://msdn.microsoft.com/en-us/library/ms180878.aspx#ISO8601Format or simply use that wonderful tool: GOOGLE. You will find plenty of shallow blogs and even some deeper ones that tell you all the gory details.
So coming back to why I am writing this blog: there is one and ONLY one reasons. When someone runs into this problem and posts a request for help on any SQL forum, I want to be able to post a reply saying something to the effect “see the reason here”. That will make me look like a very smart guy who has all the answers – the Jedi Master of SQL. (For those of you who are old enough to remember the TV show Home Improvement, I am grunting like Tim Allen as I am typing this: http://www.youtube.com/watch?v=0V9YZ7C88iU )