How to exclude ISNULL from WHERE condition with multiple conditions?

I have the following data:

DECLARE @tbl TABLE (
    Id INT,
    [Date_1] DATETIME2,
    [Date_2] DATETIME2
)

INSERT INTO @tbl ([Id], [Date_1], [Date_2]) VALUES
(1, '2018-08-15 16:05:20.000', NULL),
(2, '2018-08-15 16:05:20.000', NULL),
(3, '2018-08-15 16:05:20.000', NULL),
(4, '2018-08-20 00:00:00.000', NULL),
(5, NULL, NULL),
(6, '2018-08-30 14:02:08.000', '2018-09-05 12:31:30.530')

And I have a query which perfectly works with various conditions:

SELECT *
FROM @tbl AS t
WHERE
    (ISNULL(t.[Date_1], '1900-1-1') BETWEEN @DateFrom AND @DateTo) OR
    (ISNULL(t.[Date_2], '1900-1-1') BETWEEN @DateFrom AND @DateTo)

The first case.

DECLARE @DateFrom DATE = '1900-01-01', @DateTo DATE = '9999-12-30' -- result one. The overall count is 6 rows

Result would be:

The second case. If I change @DateFrom and @DateTo:

DECLARE @DateFrom DATE = '2018-08-15', @DateTo DATE = '2018-08-16' -- result two. The overall count is 3 rows

Then result would be:

The third case. If I change @DateFrom and @DateTo:

DECLARE @DateFrom DATE = '2018-09-05', @DateTo DATE = '2018-09-06' -- result three. The overall count is 1 row

Then result would be:

My question is how can it possible to exclude ISNULL from WHERE statement? I've tried different approaches, but they give me incorrect results:

SELECT *
FROM @tbl AS t
WHERE
((t.[Date_1] IS NOT NULL AND t.[Date_1]  BETWEEN @DateFrom
    AND @DateTo) OR t.[Date_1] IS NULL) OR
((t.[Date_2] IS NOT NULL AND t.[Date_2] BETWEEN @DateFrom
    AND @DateTo) OR t.[Date_2] IS NULL)

Any help would be greatly appreciated. I cannot set @DateFrom and @DateTo to NULL.

Solution 1:

First off, using BETWEEN to compare datetime datatype is incorrect. For example your original query does not allow you to select all 2018-08-15 dates but exclude the exact value of 2018-08-16 00:00:00.

Secondly it looks like you want a "catch all" query.

I would use the following WHERE clause:

WHERE Date_1 >= @DateFrom AND Date_1 < @DateTo
OR    Date_2 >= @DateFrom AND Date_2 < @DateTo
OR    @DateFrom IS NULL AND @DateTo IS NULL

Then set the values as follows:

-- catch all
SET @DateFrom = NULL
SET @DateTo   = NULL

-- all dates between 2018-08-15 00:00 and 2018-08-15 23:59:59.9999999
SET @DateFrom = '2018-08-15'
SET @DateTo   = '2018-08-16'

-- all dates between 2018-09-05 00:00 and 2018-09-05 23:59:59.9999999
SET @DateFrom = '2018-09-05'
SET @DateTo   = '2018-09-06'

Solution 2:

It looks like you just want all records when (@DateFrom, @DateTo) are the minimum & maximum date literals.

Then this query returns that.

SELECT  * 
FROM @tbl AS t     
WHERE ( (t.[Date_1] >= @DateFrom AND t.[Date_1] < DATEADD(day,1,@DateTo)) 
     OR (t.[Date_2] >= @DateFrom AND t.[Date_2] < DATEADD(day,1,@DateTo))
     OR (@DateFrom <= '1900-01-01' AND @DateTo >= '9999-01-01')
);

The BETWEEN were replaced since Date_1 and Date_2 aren't DATE types.

Demo

DECLARE @tbl TABLE 
(
    Id int,
    [Date_1] DateTime2,
    [Date_2] DateTime2
)
Insert Into @tbl ([Id],[Date_1],[Date_2])
Values 
  (1, '2018-08-15 16:05:20.000', NULL)
, (2, '2018-08-15 16:05:20.000', NULL)
, (3, '2018-08-15 16:05:20.000', NULL)
, (4, '2018-08-20 00:00:00.000', NULL)
, (5, NULL,NULL)
, (6, '2018-08-30 14:02:08.000', '2018-09-05 12:31:30.530');

DECLARE @DateFrom DATE, @DateTo DATE;

SELECT @DateFrom = '1900-01-01', @DateTo = '9999-12-30'; 
-- result 1.  The overall count is 6 rows
    
SELECT  * 
FROM @tbl AS t     
WHERE ( (t.[Date_1] >= @DateFrom AND t.[Date_1] < DATEADD(day,1,@DateTo)) 
     OR (t.[Date_2] >= @DateFrom AND t.[Date_2] < DATEADD(day,1,@DateTo))
     OR (@DateFrom <= '1900-01-01' AND @DateTo >= '9999-01-01')
);
       
SELECT @DateFrom = '2018-08-15', @DateTo = '2018-08-16';
-- result two. The overall count is 3 rows   

SELECT  * 
FROM @tbl AS t     
WHERE ( (t.[Date_1] >= @DateFrom AND t.[Date_1] < DATEADD(day,1,@DateTo)) 
     OR (t.[Date_2] >= @DateFrom AND t.[Date_2] < DATEADD(day,1,@DateTo))
     OR (@DateFrom <= '1900-01-01' AND @DateTo >= '9999-01-01')
);

SET @DateFrom = '2018-09-05'; SET @DateTo = '2018-09-06';
-- result three. The overall count is 1 row

SELECT  * 
FROM @tbl AS t     
WHERE ( (t.[Date_1] >= @DateFrom AND t.[Date_1] < DATEADD(day,1,@DateTo)) 
     OR (t.[Date_2] >= @DateFrom AND t.[Date_2] < DATEADD(day,1,@DateTo))
     OR (@DateFrom <= '1900-01-01' AND @DateTo >= '9999-01-01')
);
GO

Id | Date_1                      | Date_2                     
-: | :-------------------------- | :--------------------------
 1 | 2018-08-15 16:05:20.0000000 | null                       
 2 | 2018-08-15 16:05:20.0000000 | null                       
 3 | 2018-08-15 16:05:20.0000000 | null                       
 4 | 2018-08-20 00:00:00.0000000 | null                       
 5 | null                        | null                       
 6 | 2018-08-30 14:02:08.0000000 | 2018-09-05 12:31:30.5300000

Id | Date_1                      | Date_2
-: | :-------------------------- | :-----
 1 | 2018-08-15 16:05:20.0000000 | null  
 2 | 2018-08-15 16:05:20.0000000 | null  
 3 | 2018-08-15 16:05:20.0000000 | null  

Id | Date_1                      | Date_2                     
-: | :-------------------------- | :--------------------------
 6 | 2018-08-30 14:02:08.0000000 | 2018-09-05 12:31:30.5300000

db<>fiddle here