Error in when comparing British format date in where clause

When I run this in sql server2005 I got error.

select * from productratedates 
where RateDate BETWEEN '31/10/2009' AND '03/11/2009'

Error: The conve开发者_开发技巧rsion of a char data type to a datetime data type resulted in an out-of-range datetime value.

But When I run this in SQl server 2005. It is perfectly all right.

select * from productratedates 
where RateDate BETWEEN '2009-10-31' AND '2009-11-03'

I want to compare date in British format.

Any help!!

Your comment says you'd like to enter dates in the day/month/year format. You can choose that format using SET DATEFORMAT:

SET DATEFORMAT dmy;

For example:

set dateformat dmy
select cast('31/10/2009' as datetime) -- succeeds
set dateformat mdy
select cast('31/10/2009' as datetime) -- fails

You can retrieve the current dateformat setting with DBCC:

dbcc useroptions

A list of available languages, with their dateformat, is available from:

exec sp_helplanguage

For me, the language called "British" has dateformat dmy. So you can change the default language for your login to British (from the property page of your login.) You can even specify it in the connection string:

Server=<server>;Uid=<login>;Pwd=<password>;Current Language=British

You could convert the date to YYYY-MM-DD format before you send it to the server.

Dates get read in US format where possible, so '31/10/2009' has to be UK format, but '03/11/2009' flips over to 11th March. That messes up your BETWEEN by going backwards in time.

I don't know if it would work for you, but we always use the format dd-mmm-yyyy:

select * from productratedates where RateDate BETWEEN '31-oct-2009' AND '03-nov-2009'

How a date is formatted is actually an interface thing. If you are looking purely at the data dates should ALWAYS be in one specific format YYYY/MM/DD.

Your interface is responsible for displaying the date in the localized format. By using this practice the script is ambiguous about where it is used and what language it is. so comparing the date should always be done in the standardized format.

What I suggest you to do is have your interface show it in the format you like and the back-end (including SQL statements) to be the standardized date format.