What is the best format for date literals to avoid SQL Server language setting problems?
Posted on 2005-04-29
SQL Server 2000.
I am trying to fix a bug with a SQL statement where a date literal is rejected by SQL Server. The literal is in the form 'yyyy-mm-dd', e.g. INVOICE_DATE<='2005/4/29'.
This is fine when the server language is "English" but fails when the language is "British English", with the error:
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
What I would like to know is if there is a safe date literal format in SQL Server that will be accepted regardless of language setting? For example I changed the above statement to INVOICE_DATE<='2005/APR/29' which was accepted, but I don't know if this would work with French or non-English language settings.