Datediff less than is not working

1

Hello,
my query for >0 works but it doesn't work for <150. Please help.
SELECT s.Title,
i.OpenClosed,
s.Supplier,
cast(s.InspectionDate as date) as InspectionDate,
cast(s.FinalInspReportCreated as date) as FinalInspReportCreated,
cast(i.FinalPackingList as date) as FinalPackingList,
cast(i.Departure as date) as Departure,
datediff(d,cast(s.InspectionDate as date), cast(i.Departure as date)) as DaysToShip
FROM [db_owner].[SPViews.LIM_Supplier_Table] s
INNER JOIN [db_owner].[SPViews.LIM_Inspect-Ship-Deliver] i ON s.Title = i.Title
where i.departure is not null
and left(cast(s.InspectionDate as date),4) >= '2014'
and datediff(d,cast(s.InspectionDate as date), cast(i.Departure as date))<=150
and datediff(d,cast(s.InspectionDate as date), cast(i.Departure as date))>0

2

Would you please provide the table structure and example data?

3

Personally I would use some code like this as I think it is more readable and probably more efficient (fewer functions performing manipulation on column data and thus more chance that SQL will use an index)

and s.InspectionDate >= cast(i.Departure as date)
and s.InspectionDate <= DATEADD(Day, 150, cast(i.Departure as date))

I'm a bit iffy about your start point. If Departure is today is it OK if InspectionDate is today? or must that be tomorrow as the earliest value? (Or would InspectionDate being after Departure be OK if the comparison included time? i.e. it could be 1 minute after Departure)

4

You always to avoid using functions on key columns in the WHERE clause whenever possible. In this case, one of the two columns must use a function. Since you're listing from table "s.", I've adjusted the "i." table's column:

where i.departure is not null
and s.InspectionDate >= '20140101'
and s.InspectionDate >= dateadd(day, datediff(day, 0, i.Departure) - 150, 0)
and s.InspectionDate < dateadd(day, datediff(day, 0, i.Departure), 0)
5

Thank you, all! I was using the query in Data Tools to build a chart and it wasn't working for <=. When I ran it in SQL, it worked fine. Thanks again!

6

Do you think that

and s.InspectionDate < cast(i.Departure as date)

would work there, and be SARGable?

7

The CAST is not technically directly SARGable. But in SQL Server, the optimizer "knows" to treat dates as a type of datetime so it almost certainly will be processed as such. But if the column is datetime -- which it appears to be here -- I personally would compare another datetime to it, esp. as that column is already a datetime also.

1 Like
8

I would be inclined to try adding

and s.InspectionDate < i.Departure

to the WHERE clause. That should allow an index to be used, and then the

and s.InspectionDate < dateadd(day, datediff(day, 0, i.Departure), 0)

will fine-filter it