select * from date_range_table
where
((@startdate1 IS NULL AND @enddate1 IS NULL)
OR (start_date1 >= @startdate1 AND start_date1 <= @enddate1)
OR (start_date1 >= @startdate1 AND @enddate1 IS NULL)
OR (@startdate1 IS NULL AND start_date1 <= @enddate1))
AND
((@startdate2 IS NULL AND @enddate2 IS NULL)
OR (start_date2 >= @startdate2 AND start_date2 <= @enddate2)
OR (start_date2 >= @startdate2 AND @enddate2 IS NULL)
OR (@startdate2 IS NULL AND start_date2 <= @enddate2))
AND
((@startdate3 IS NULL AND @enddate3 IS NULL)
OR (start_date3 >= @startdate3 AND start_date3 <= @enddate3)
OR (start_date3 >= @startdate3 AND @enddate3 IS NULL)
OR (@startdate3 IS NULL AND start_date3 <= @enddate3))
OR IF YOU NEED TO CAST THE TIME DO THIS:
WHERE
((@startdate1 IS NULL AND @enddate1 IS NULL)
OR (start_date1 >= (@startdate1 + cast('00:00:00.000' as smalldatetime)) AND start_date1 <= (@enddate1 + cast('23:59:59.999' as smalldatetime)))
OR (start_date1 >= (@startdate1 + cast('00:00:00.000' as smalldatetime)) AND @enddate1 IS NULL)
OR (@startdate1 IS NULL AND start_date1 <= (@enddate1 + cast('23:59:59.999' as smalldatetime))))
AND
((@startdate2 IS NULL AND @enddate2 IS NULL)
OR (start_date2 >= (@startdate2 + cast('00:00:00.000' as smalldatetime)) AND start_date2 <= (@enddate2 + cast('23:59:59.999' as smalldatetime)))
OR (start_date2 >= (@startdate2 + cast('00:00:00.000' as smalldatetime)) AND @enddate2 IS NULL)
OR (@startdate2 IS NULL AND start_date2 <= (@enddate2 + cast('23:59:59.999' as smalldatetime))))
AND
((@startdate3 IS NULL AND @enddate3 IS NULL)
OR (start_date3 >= (@startdate3 + cast('00:00:00.000' as smalldatetime)) AND start_date3 <= (@enddate3 + cast('23:59:59.999' as smalldatetime)))
OR (start_date3 >= (@startdate3 + cast('00:00:00.000' as smalldatetime)) AND @enddate3 IS NULL)
OR (@startdate3 IS NULL AND start_date3 <= (@enddate3 + cast('23:59:59.999' as smalldatetime))))
I have found that a ‘CASE WHEN’ http://www.jamesandchey.net/?p=119 will work fine when working with one date range, but with multiple date ranges, the results can be unpredictable. The above logic proves to be a more effective way to get the results your are looking for.
This SQL says this:
If the @start date and @end date are NULL, get all rows. Basically ignore this where clause.
If your @start date is defined and @end date is defined, get all rows within the start and end date range
If your @start date is defined and @end date is NULL, the date range has no end, so get all rows after the range’s start.
If your @start date is NULL and @end date is defined, the date range has no start, so get all rows before the range’s end.
