SQL Query Date between, if date column not null

Question

I have a query as follows

SELECT ajd.Make, ajd.Model, ajd.Filter, ajd.Notes, ajd.CatamacPartNumber, ajd.FromDate, ajd.ToDate 
  FROM ApplicationJapData ajd 
  WHERE ajd.Model LIKE '%FVR34%' 
  AND FromDate <= '20140701' AND ToDate >= '20140701'

What this query does is select rows based on Model containing certain search string, and where supplied date is between columns FromDate and ToDate.

The problem is that sometimes ToDate is null, because it is up to current date (For instance, FromDate = 1/1/2015, and ToDate is null because it is 1/1/12 - Present)

So if I supply a value of 12-12-2015, I would still like to return rows if it meets the FromDate condition.

However, I can't use OR in FromDate <= '20140701' OR ToDate >= '20140701' because there might be ToDate of 1-6-15, in which case it would be incorrect with supplied date of 12-12-2015

Any help is much appreciated, and please ask if anything is unclear or needs clarifying!

Your requirement is not clear. Are you worried about different date formats, or do you want to somehow handle `NULL` to and from dates? — Tim Biegeleisen, Nov 04 '16 at 01:10
Sorry for the confusion Tim. I just wanted to handle Null dates. — Brendan Gooden, Nov 04 '16 at 02:09

score 3 · Accepted Answer · answered Nov 04 '16 at 01:08

3

AND FromDate <= '20140701' 
AND (ToDate is null OR ToDate >= '20140701')

answered Nov 04 '16 at 01:08

juergen d

201,996
37
293
362

Will work, but unfortunately many query optimizers still seem to struggle with OR – Mark Adelsberger Nov 04 '16 at 01:11
1

@MarkAdelsberger - please tell us why. – MasterJoe Nov 04 '16 at 01:13
@BrendanGooden You should also read Gordon's answer which discusses another method for dealing with `NULL` dates, which might perform better if you have an index on the `ToDate` column. – Tim Biegeleisen Nov 04 '16 at 02:11
Thanks Tim. Can you explain the indexing on `ToDate` ? – Brendan Gooden Nov 04 '16 at 02:13
@testerjoe2 - not sure what you want me to tell you. Observationally many query optimizers still have trouble with OR. I could speculate about their inner workings, but query optimization is insanely complex and depends heavily on the DBMS. If this works and performs well in a given situation, that's fine; I'm merely pointing out that it might not, and if not there are other options. (And as a rule of thumb, I personally use OR as a last resort when there are multiple ways, because of what I've seen performance-wise.) – Mark Adelsberger Nov 04 '16 at 13:08

score 1 · Answer 2 · answered Nov 04 '16 at 01:12

The canonical methods are:

SELECT ajd.Make, ajd.Model, ajd.Filter, ajd.Notes, ajd.CatamacPartNumber, ajd.FromDate, ajd.ToDate 
FROM ApplicationJapData ajd 
WHERE ajd.Model LIKE '%FVR34%' AND
      FromDate <= '20140701' AND (ToDate >= '20140701' or ToDate IS NULL)

or:

WHERE ajd.Model LIKE '%FVR34%' AND
      FromDate <= '20140701' AND
      coalesce(ToDate, '20140701') >= '20140701'

Under some circumstances, you may want to pick a distant future date as the end date -- so the value is never NULL. This can be beneficial in encouraging index usage on queries using the table.

score 0 · Answer 3 · answered Nov 04 '16 at 01:10

The simplest solution would be to coalesce to_date with a far-future date, such as

SELECT ajd.Make, ajd.Model, ajd.Filter, ajd.Notes, ajd.CatamacPartNumber, ajd.FromDate, ajd.ToDate 
  FROM ApplicationJapData ajd 
 WHERE ajd.Model LIKE '%FVR34%' 
   AND FromDate <= '20140701' AND coalesce(ToDate,'99991231') >= '20140701'

score 0 · Answer 4 · answered Nov 04 '16 at 01:13

The simplest way to implement your logic would be to explicitly allow all NULL values in the to date:

SELECT ajd.Make,
       ajd.Model,
       ajd.Filter,
       ajd.Notes,
       ajd.CatamacPartNumber,
       ajd.FromDate,
       ajd.ToDate 
FROM ApplicationJapData ajd 
WHERE ajd.Model LIKE '%FVR34%' AND
      FromDate <= '20140701' AND (ToDate >= '20140701' OR ToDate IS NULL)

score 0 · Answer 5 · edited May 23 '17 at 10:32

Similar to the answers above. IsNull performs better than the generic Coalesce and better than having another AND.

   SELECT   ajd.Make, 
            ajd.Model, 
            ajd.Filter, 
            ajd.Notes, 
            ajd.CatamacPartNumber, 
            ajd.FromDate, 
            ajd.ToDate 
  FROM      ApplicationJapData ajd 
  WHERE     ajd.Model LIKE '%FVR34%' 
        AND FromDate <= '20140701' 
        AND IsNull(ToDate,'99991231') >= '20140701'

SQL Query Date between, if date column not null

5 Answers5