CODEWITHSUNDEEP
sqlsql-serverdatedatetime
Brendan Gooden
Brendan Gooden

Reputation: 1551

SQL Query Date between, if date column not null

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!

Upvotes: 0

Views: 8640

Answers (5)

UV.
UV.

Reputation: 492

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'

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

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)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269793

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.

Upvotes: 1

Mark Adelsberger
Mark Adelsberger

Reputation: 45649

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'

Upvotes: 0

juergen d
juergen d

Reputation: 204766

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

Upvotes: 3

Related Questions