SQL Date Format YYYY MM DD: A Standardized and Universal Format

When reviewing code in my consulting work, one of the tells that I look for to spot an inexperienced SQL programmer is a date format that is not standardized. Another reason we picked this format is that it is the most common embedded display format among all national options. This is why Microsoft defaults to this format in its current implementations and will eventually get rid of the old local dialects.

We deliberately chose this format to make dates uniform and easy to parse because it's not ambiguous. The only format permitted in the ANSI/ISO standards is the 8601 version with dashes, which may be the reason why they had to vote on them so many years ago when I was on the committee.

There are times when I think I'm the only person who reads the ANSI standards. However, there are some ODBC scalar functions that are documented and supported, such as 'yyyyMMdd'. But this format is not even documented (so not sure if it's even officially supported) and it's not self-documenting either.

I wonder why SQL Server uses 'easier' than just removing the dashes and using a string literal that is more universally supported. For example, you can use the format 'YYYYMMDD' which is easier to read and write.

If you allow end users to type a date into a text field, you have zero confidence in the data being entered correctly. It's better to use a calendar or date picker when accepting user input from a form.

Incorrect data can lead to issues until the first error happens, and you may not notice this issue until you misinterpret a date that actually breaks. For example, if you start testing on January 1st and use today's date as the literal, you might just not notice you are storing incorrect dates.

Make sure you are properly handling date formats and not making any assumptions about the user's or application's language or dateformat settings. It's also important to note that SQL Server behaves this way for a reason, which is to make it easier for developers to work with dates in a standardized way.

In conclusion, using the SQL date format YYYY MM DD is a good practice because it is standardized and universal, making it easy to parse and understand. By following this format, you can ensure that your data is accurate and reliable, and avoid any issues that may arise from incorrect date formatting.

Sql date format yyyy mm dd