SQL Server: date and time error out of range

Question

I have this request

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 will respond with

Converting a char data type to a datetime data type resulted in a datetime time value out of range.

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 similar to '09 / 03/2012 'and this is the input from jquery ui datepicker with date format mm / dd / yyyy

Please help me, I do not know how to fix this.

I am working on SQL Server 2005.

EDIT: I tried the same queries with another sql 2005 server and it works fine, please, please, how to fix this error using sql server settings

Answer 1

I assume your WDATE is varchar / char and not datatime, as it should be, you can use it like that, but I recommend that you change the data type to datetime. Try the following:

 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 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]'