where close
-
I am getting an error msg: "The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value." when I have the following code in where close: and n.dateentered between (case When DatePart (dw , getdate())=2 Then (getDate()-3 ) When DatePart (dw , getdate())=4 Then (getDate()-2 ) When DatePart (dw , getdate())=6 Then (getDate()-2 ) Else GetDate() End ) and (getdate()-1) but when I run with this (I put dates manually) it is fine. and n.dateentered between '09/20/2003' and '09/29/2003' Any idea why?
-
I am getting an error msg: "The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value." when I have the following code in where close: and n.dateentered between (case When DatePart (dw , getdate())=2 Then (getDate()-3 ) When DatePart (dw , getdate())=4 Then (getDate()-2 ) When DatePart (dw , getdate())=6 Then (getDate()-2 ) Else GetDate() End ) and (getdate()-1) but when I run with this (I put dates manually) it is fine. and n.dateentered between '09/20/2003' and '09/29/2003' Any idea why?
I have tested the SQL query using SQL Server Enterprise Manager and it's working fine for me. My guess is that n.dateentered is of type "smalldatetime" and GetDate actually returns "datetime" instead. You might want to try one of the following: 1. Use CAST(x as y) e.g. CAST( (case when...end) AS smalldatetime) ->I've tested this with the code and it works 2. Use DateAdd() function instead of getDate()-3 3. Use DatePart to format your date ranges to yyyymmdd format