SQL server VS2005 Between Date problem

[Monty sing]

I am facing problem while fetching a data from SQL server DB

below is a query i'm executing

select * from Customer where(OrderDate BETWEEN '02/01/2012' and '14/01/2012') and (Item='Dell')

This query was working correct 2 days back but today i executed the same query and showing below error

"The Conversion of char datatype to a datetime datatype resulted in an out-of-range datetime value"

IN MY Sql Database i'm using Datatype datetime to store dates

[C CSR]

Should your output be 01/02/2012 and 01/14/2012? There is no 14th month. If your data is wrong, check your input.

[NeoPa]

Monty Sing:
This query was working correct 2 days back but today I executed the same query and showing below error

No. It wasn't. You simply failed to realise it was getting the dates wrong. What you intended as 12th Jan was interpreted (correctly. Your code is where the error is) as 1st Dec.

Your SQL code is being created somewhere, no doubt, and this is getting the dates wrong. SQL dates are not region dependent. They recognise a general (ANSI-92) SQL standard format hich is m/d/y, regardless of where in the world your SQL is running. Hence, your code should be :

SELECT * FROM [Customer] WHERE ([OrderDate] BETWEEN '1/2/2012' AND '1/14/2012') AND ([Item]='Dell')

assuming your date range is 2nd Jan 2012 to 14th Jan 2012. Whatever code you have that creates this string should also be amended to reflect this change.