CODEWITHSUNDEEP
sqlsql-server
Steven Duncan
Steven Duncan

Reputation: 39

SQLError with dateadd function / time functions

I am fairly new to SQL. I am making a query with the parameters to filter out any objects that has been shipped over 6 months ago, and (after checking other variables) if it has been shipped within the past 21 days. The code will work if I keep the last line commented out (I will show you below), but when I uncomment this line, the code gives me the error:

Conversion failed when converting date and/or time from character string.

I've been trying things and researching, but I am still having trouble. Your help would be greatly appreciated. Code below:

where i.status IN ('CR','LB','OB')
and i.FGCs > 0
and i.LastShpd > CAST(CONVERT(CHAR(11),DATEADD(DAY,-180,GETDATE()),113) AS datetime)
and (n.LastShpd IN ('0', '') 
     and (i.OnOrder IN ('0', '') 
     or i.LastShpd < CAST(CONVERT(CHAR(11),DATEADD(DAY,-21,GETDATE()),113) AS datetime2)))

Upvotes: 0

Views: 107

Answers (2)

Isaac
Isaac

Reputation: 3363

Given the cast to datetime or datetime2 before comparing to i.LastShpd I am assuming the data type of i.LastShpd date related. Therefore, I think using DATEDIFF instead of DATEADD will simplify your query...

where i.status IN ('CR','LB','OB')
and i.FGCs > 0
and datediff(day, getdate(), i.LastShpd) < -180
and (n.LastShpd IN ('0', '') 
     and (i.OnOrder IN ('0', '') 
     or datdiff(day, getdate(), i.LastShpd) < -21))

Upvotes: 1

Marin Mohanadas
Marin Mohanadas

Reputation: 163

This is your query:

where i.status IN ('CR','LB','OB')
and i.FGCs > 0
and i.LastShpd > CAST(CONVERT(CHAR(11),DATEADD(DAY,-180,GETDATE()),113) AS datetime) 
and (n.LastShpd IN ('0', '') -- I'm assuming error occurred in this point
     and (i.OnOrder IN ('0', '') 
     or i.LastShpd < CAST(CONVERT(CHAR(11),DATEADD(DAY,-21,GETDATE()),113) AS datetime2)))

try this :

FORMAT (n.LastShpd, 'd', 'en-US' ) in ('0','')

Once you use the FORMAT, SQL converts that values to varchar. Then you can apply your string conditions.

Upvotes: 1

Related Questions