why iso format is recommended while passing date values?

The reason for posting is because there have been at least a dozen times when I've come across posts from others stating problems in passing datetime values to sql query. Mostly this happens when the date values are being passed from some third party apps to sql code.

A typical error that worries everyone working with date values is

Msg 241, Level 16, State 1, Line 3
Conversion failed when converting datetime from character string.

Most occasions above error is generated when you're trying to pass constant datetime value to a variable or parameter. To understand the cause of it we will first understand how dates are stored and interpreted in SQL Server.

In SQL Server, date values are stored as integer equivalents internally. An integer value of 0 corresponds to a base date value of 1900-01-01 00:00:00. Every 1 integer unit represents a day. You can check this by below code

DECLARE @d datetime
SET @D=0
SELECT @d,@d+1,@d-5

Now lets see how SQL Server interprets the passed date value. Two main factors which affect the interpretation of date values in SQL Server are language setting and date format setting. By default when you install SQL server there will be a language setting associated with it. You can override it by means of SET LANGUAGE setting.
The list of supported languages can be found from below system catalog

select * from syslanguages

As you see from above screenshot each language has a dateformat associated with it. So any date values passed will be interpreted based on that

As an example see the code results below

As you see the same value passed is interpreted differently in different language settings. It all worked fine as date value passed was valid in all formats. Now change date to say 23/11/10 and see what happens

As you see, it will cause some formats to throw an error as in such formats (in this case mdy) date value passed is invalid (23 is not a valid month!!)

From above, we've seen problems of sending the date in ambiguous formats where we leave it to server to interpret the date values

Now we can see what happens if we sent month names explicitly in date values

Here also as you see it wont work well in all cases. This is because month names are also dependent on language settings which is why it fails in some cases.

So the only reliable format we can use for sending the date values regardless of server setting is universally accepted iso standard.

So always make it a point to send date values in CCYYMMDD HH:MM:SS format which is iso format. Even if your application returns dates in some other format you can apply format functions to make it in iso format before you pass it down to sql code to make it server independent.