Why use Date as YYYYMMDD in SQL Server?

When you enter a Date in SQL SERVER for inserting data or filtering data in WHERE clause, what you enter is String. SQL Server infernally converts that formatted String to different date and time data types in Java e.g. DATETIME, DATE, TIME, DATETIME2 and SMALLDATETIME. This conversion is subject to language setting in SQL SERVER, for example, the same date 03/07/2015 can be interpreted as 3rd March 2015 if language setting is US English (MMDDYYYY) and 3rd July 2015 if language is set to British English(DDMMYYYY). To avoid this, you can enter date as YYYYMMDD format, which is considered language neutral and always interpreted correctly by Microsoft SQL Server.

For example, the date 20150709 will always interpreted as 9th July 2015 by SQL Server, irrespective of language setting. This is why it's considered best practice to enter date as YYYYMMDD String in Microsoft SQL Server. 

Important points

1) When a language setting is applied in Microsoft SQL Server, it sets many variable respective to that language and one of them is DATEFORMAT. That's why a String not entered in language neutral way will be interpreted differently by different SQL Server instances with different language settings. 

2) Same date 07-12-15 can be interpreted as 7th December 2015 if language is set to en-UK, 12th July if language is American English.

3) If you don't want that SQL Server implicitly convert VARCHAR to DATETIME type, you can use PARSE() or CONVERT() function to provide a formatting style. This will ensure that SQL SERVER interpret the String in the same date format irrespective of language setting e.g. 

SELECT CONVERT(DATETIME, '09-10-2015', 104); 

will ensure that given date is always on DD-MM-YYYY format, also known as British Date format. 

4) The language setting only affect the String to Datetime conversion for inserting data into SQL SERVER. It won't affect the presentation of that data into SQL client e.g. ODBC or SQL SERVER Management Studio.

5) In SQL Server 2008, separate DATE and TIME data types were introduced, they also have couple of other DATEFORMAT which is language neutral, e.g. YYYY-MM-DD (2015-07-31) is also language neutral for DATE type but due to legacy reasons it's not language neutral for DATETIME and SMALLDATETIME types. On the other hand, YYYYMMDD is language neutral for all of date and time data types in SQL SERVER. 

6) When Microsoft SQL Server converts a String to DATETIME which only contains date part, it considered time ad midnight and enter '00:00:00.000' for time part. This is very important detail to know if your DATETIME column contains both date and time part. Why? because it can create subtle bugs while comparing Date in Java as shown here.

That's all about Why should you always enter date as "YYYYMMDD" in SQL SERVER. This will avoid the bug created by different language setting at different SQL SERVER instances. It's the language neutral format for all SQL SERVER DATE and TIME data types including DATETIME, DATE, TIME, DATETIME2 and SMALLDATETIME.  

You can also further read Microsoft SQL SERVER 2012 T-SQL Fundamentals and Querying Microsoft SQL SERVER 2012, the 70-461 Exam Guide to learn more about SQL Server details like this.

Other related SQL queries, Interview questions, and articles:

• How to find the second highest salary in a table? (solution)
• Top 5 Books to learn Advanced SQL and Database Design (books)
• Write a SQL query to copy or backup a table in MySQL (solution)
• Difference between the Unique and Primary keys in the table? (answer)
• How to migrate SQL queries from Oracle to SQL Server 2008? (answer)
• Difference between clustered and non-clustered indexes in SQL? (answer)
• What is the difference between UNION and UNION ALL in SQL? (answer)
• Top 5 Courses to learn MySQL Database for Beginners (Courses)
• 5 Free Courses to learn T-SQL and SQL Server for Beginners (Courses)
• Top 5 Websites to learn SQL online for FREE? (resource)
• 5 Best PostgreSQL Courses for Beginners (online courses)
• 5 Courses to learn Oracle and Microsoft SQL Server database (courses)
• Difference between View and Materialized View in Database? (answer)
• 5 Courses to learn Database and SQL Better (courses)
• 10 Free SQL and Database Courses for Beginners (free courses)

Thanks for reading this article so far. If you like these  SQL tutorial then please share them with your friends and colleagues. If you have any questions or feedback then please drop a note.

P.S. - If you are interested in learning Database and SQL and looking for some free resources to start your journey then you can also take a look at this list of Free SQL Courses for Beginners to kick-start your learning.