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
– Tim Biegeleisen, Commented Nov 4, 2016 at 1:10
Sorry for the confusion Tim. I just wanted to handle Null dates. — Brendan Gooden
– Brendan Gooden, Commented Nov 4, 2016 at 2:09

juergen d · Accepted Answer · 2016-11-04 01:08:00Z

3

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

answered Nov 4, 2016 at 1:08

juergen d

205k40 gold badges305 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mark Adelsberger Over a year ago

Will work, but unfortunately many query optimizers still seem to struggle with OR

MasterJoe Over a year ago

@MarkAdelsberger - please tell us why.

Tim Biegeleisen Over a year ago

@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.

Brendan Gooden Over a year ago

Thanks Tim. Can you explain the indexing on ToDate ?

Mark Adelsberger Over a year ago

@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.)

Gordon Linoff · Accepted Answer · 2016-11-04 01:12:14Z

1

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.

answered Nov 4, 2016 at 1:12

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Mark Adelsberger · Accepted Answer · 2016-11-04 01:10:23Z

0

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'

answered Nov 4, 2016 at 1:10

Mark Adelsberger

46.6k4 gold badges42 silver badges60 bronze badges

Comments

Tim Biegeleisen · Accepted Answer · 2016-11-04 01:13:06Z

0

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)

answered Nov 4, 2016 at 1:13

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 10:32:39Z

0

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'

edited May 23, 2017 at 10:32

CommunityBot

11 silver badge

answered Nov 4, 2016 at 1:20

UV.

4926 silver badges9 bronze badges

Collectives™ on Stack Overflow

SQL Query Date between, if date column not null

5 Answers 5

5 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

5 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related