SQL Server: datetime out of range error

Question

I have this query

SELECT W.NUMBER,B.NAME,Br.NAME,W.WDATE,W.REPAIRSTATUS,W.REPAIRCOST,W.REMARK
FROM Work_Order AS W,Brands AS B,Branches AS Br
WHERE W.BRANDID = B.ID AND W.BRANCHID = Br.ID
AND CAST(WDATE as DATETIME)  < CAST('09/18/2012' AS DATETIME)

and the system respond with

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

also using this query it gives the same error

SELECT W.NUMBER,B.NAME,Br.NAME,W.WDATE,W.REPAIRSTATUS,W.REPAIRCOST,W.REMARK
FROM Work_Order AS W,Brands AS B,Branches AS Br
WHERE W.BRANDID = B.ID AND W.BRANCHID = Br.ID
ORDER BY CAST(W.WDATE AS DATETIME)

WDATE is like '09/03/2012' and it is an input from jquery ui datepicker with mm/dd/yyyy date format

Please help me I don't know how to fix it.

I am working on SQL Server 2005.

EDIT: I tried the same queries with another sql server 2005 and it's working fine so please how to fix this error with a sql server settings ??

Answer 1

I assume your WDATE is a varchar / char and not datatime as it should be, you can cast it like this, however i recommand that you change the datatype to datetime. Try this:

SELECT W.NUMBER,B.NAME,Br.NAME,W.WDATE,W.REPAIRSTATUS,W.REPAIRCOST,W.REMARK 
FROM Work_Order AS W,Brands AS B,Branches AS Br 
WHERE W.BRANDID = B.ID AND W.BRANCHID = Br.ID 
AND CONVERT( DATETIME, WDATE, 101) < CONVERT( DATETIME, '09/18/2012', 101)

Try this code, it should find most of the invalid dates

SELECT WDATE, W.NUMBER,B.NAME,Br.NAME,W.WDATE,W.REPAIRSTATUS,W.REPAIRCOST,W.REMARK 
FROM Work_Order AS W,Brands AS B,Branches AS Br 
WHERE W.BRANDID = B.ID AND W.BRANCHID = Br.ID 
AND WDATE not like '[0-1][0-9]/[0-3][0-9]/20[0-1][0-9]'