CODEWITHSUNDEEP
sql-serversql-server-2008-r2
OSBastard
OSBastard

Reputation: 135

Refine SQL date-range query

Attempting to query for when, given parameters @startDate and @endDate, a record occurs in as much of the time frame as is supplied. I have a functional where clause below, but I suspect it can be more direct.

If an end date is supplied, records will not be selected from after that date. If a start date is supplied, records will not be selected from before that date. If no dates are supplied all records will be selected.

SELECT * 
FROM myTable
WHERE
   (
      (@startDate IS NULL AND ((@endDate IS NULL) OR (myTable.[recordDate] <= @endDate)))
      OR
      (@endDate IS NULL AND ((@startDate IS NULL) OR (myTable.[recordDate] >= @startDate)))
      OR
      (myTable.[recordDate] BETWEEN @startDate AND @endDate)
   )

Upvotes: 1

Views: 257

Answers (2)

user3537535
user3537535

Reputation: 36

Setup a temp tableceiling with floor and ceiling and join to table on date BETWEEN floor and ceiling. Setup your unbounded conditions(NULL)as 1/1/1900 and 12/31/9999. A colleague showed me this recently and it worked.

Upvotes: 0

SMA
SMA

Reputation: 37023

You could workaround with ISNULL function like below:

SELECT * FROM myTable
WHERE myTable.[recordDate] >= ISNULL(@startDate, '01/01/1900')
AND   myTable.[recordDate] <= ISNULL(@endDate, getDate())

This query will select all the rows that are either:

  • Between @startDate and @endDate inclusive of both
  • Between @startDate and current date time if @endDate is null
  • Between 01/01/1900 if @startDate is null and todays' date time if @endDate is null
  • Between 01/01/1900 if @startDate is null and @endDate

Upvotes: 1

Related Questions