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 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
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.