SQL Dates – Use ISO-8601 Format

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 )


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