CODEWITHSUNDEEP
sqlsql-servert-sql
Moose
Moose

Reputation: 5422

Filtering multiple dates in row with optional to and from parameters

I have a table with three dates:

id        date1        date2        date3
1         1945-12-13   1930-11-04   null
2         1970-09-12   1971-09-13   1972-09-14
3         null         null         null
4         2000-01-01   2001-01-01   2002-01-01

My proc passes in 2 parameters:

@dateFrom datetime = NULL,
@dateTo datetime = NULL

I want to return records that have any date in between the two. NULL for either @dateFrom or @DateTo makes it open ended.

This works for the open ended case, but not for the case where both are not null.

SELECT * 
FROM Table1
WHERE (@dateFrom IS NULL 
          OR ISNULL(date1,'12/31/1753') >= @dateFrom
          OR ISNULL(date2,'12/31/1753') >= @dateFrom 
          OR ISNULL(date3,'12/31/1753') >= @dateFrom)
  AND (@dateTo IS NULL 
          OR  ISNULL(date1,'12/31/2099') <= @dateTo
          OR ISNULL(date2,'12/31/2099') <= @dateTo
          OR ISNULL(date3,'12/31/2099') <= @dateTo)

If I pass @dateFrom = '1940-01-01' and @dateTo = '1950-01-01', I only want the first record, but I'm getting 1,2,and 4 because they all have dates > 1940.

I just can't wrap my head around how to structure this.

Column and Table names have been changed to protect the innocent.

Upvotes: 1

Views: 738

Answers (3)

Henry
Henry

Reputation: 1

Ok this is simplistic but it would work I think since it is in a proc...

declare @startDate datetimne declare @enddate datetime

if @datefrom is NULL set @startDate = '12/31/1753' else set @startDate = @datefrom

if @dateTo is NULL set @endDate = '12/31/2099' else set @endDate = @dateto

and use @datefrom and @dateto to qualify...

Upvotes: 0

Bill Hurt
Bill Hurt

Reputation: 749

Just for another way to look at it. This solution would also work. It makes the where clause simpler at the expense of an additional block of code and a join.

CREATE TABLE #dates (id INT, date1 DATE, date2 DATE, date3 DATE)

INSERT INTO #dates 
VALUES
('1','12/13/1945','11/4/1930',NULL),
('2','9/12/1970','9/13/1971','9/14/1972'),
('3',NULL,NULL,NULL),
('4','1/1/2000','1/1/2001','1/1/2002')

DECLARE
    @dateFrom datetime = '1940-01-01',
    @dateTo datetime = '1950-01-01'

;WITH dateFilter AS (

SELECT id,[Date],DateIndex
FROM
(SELECT
id, date1, date2, date3
FROM #dates) p
UNPIVOT([DATE] FOR DateIndex IN ([date1],[date2],[date3])) AS up
WHERE
    up.[DATE] BETWEEN @dateFrom AND @dateTo
)

SELECT
    d.*
FROM #dates d
INNER JOIN dateFilter df
ON df.id = d.id


DROP TABLE #dates

Upvotes: 1

JNK
JNK

Reputation: 65187

You need to filter each date field individually within the range, like so:

WHERE 
   (Date1 >= ISNULL(@DateFrom,'17531231')
    AND Date1 <= ISNULL(@dateTo,'20991231'))
OR
 (Date2 >= ISNULL(@DateFrom,'1753-12-31')
    AND Date2 <= ISNULL(@dateTo,'20991231'))
OR
 (Date3 >= ISNULL(@DateFrom,'1753-12-31')
    AND Date3 <= ISNULL(@dateTo,'20991231'))

Otherwise you aren't checking the range for each date field, just that a date in that row matches one of the criteria.

Upvotes: 3

Related Questions