CODEWITHSUNDEEP
sqlsql-server
Blake Rivell
Blake Rivell

Reputation: 13895

Return data where any part of StartDate and EndDate fall within filter date range

I have a bunch of product records with a StartDate and EndDate. I also have a StartDate and EndDate filter settings passed in as params.

The goal:
Return any products where any part of the Product Date Range falls in between or equal to the filter settings Date Range.

Here is my attempt but it is not working:

((@FilterStartDate <= prod.StartDate AND @FilterStartDate <= prod.EndDate)
OR
(@FilterEndDate >= prod.StartDate AND @FilterEndDate <= prod.EndDate))

Upvotes: 1

Views: 338

Answers (3)

S3S
S3S

Reputation: 25152

Perhaps this is what you want this simplified version to just check if the start or end falls in the range...

where
    (prod.StartDate >= @FilterStartDate and prod.StartDate <= @FilterEndDate)
    or 
    (prod.EndDate  >= @FilterStartDate and prod.EndDate <= @FilterEndDate)

Upvotes: 2

ssensi
ssensi

Reputation: 61

Assuming your dates column is actually datetime, you can try

WHERE prod.StartDate >= 'yourStartDate' AND prod.EndDate < 'yourEndDate'

or, if it's not datetime and you prefer casting:

WHERE CAST(dates as date) BETWEEN 'yourStartDate' and 'yourEndDate'.

Upvotes: 0

DavidG
DavidG

Reputation: 119186

You want to check that the filter start date is before the product end date and the filter end date is after the project start date, for example:

@FilterStartDate <= prod.EndDate 
AND @FilterEndDate >= prod.StartDate

Upvotes: 1

Related Questions