How to compare dates in MySQL


There is a table in which, among other things, there are two fields

activeFrom [date] DEFAULT NULL
activeTill [date] DEFAULT NULL

Data is entered into the table from different sources, so the fields have different values: NULL , 0000-00-00 and some date (for example) 2015-12-16 .

The challenge :
SQL query to pull out all rows where the current date falls between activeFrom and activeTill .
Moreover, if the field is NULL or 0000-00-00 , consider it as true . (i.e. if it is 2015-12-16 now, in the table activeFrom = NULL and activeTill = '2016-01-01' , then the record is needed).
activeTill – date inclusive

Basically I did by comparing all the options.

SELECT * FROM [table] 
    (activeFrom='0000-0-0' AND activeTill='0000-0-0') OR
    (activeFrom<>'0000-0-0' AND activeTill<>'0000-0-0' AND NOW() BETWEEN activeFrom AND CONCAT(activeTill, ' 23:59:59')) OR
    (activeFrom='0000-0-0' AND activeTill<>'0000-0-0' AND NOW()<CONCAT(activeTill, ' 23:59:59') OR
    (activeFrom<>'0000-0-0' AND activeTill='0000-0-0' AND activeFrom<NOW()))

But I have a suspicion that it can be solved somehow more gracefully. Without this nightmare listing.

Can someone suggest options?


select *
  from table
 where curdate() between (if(ifnull(activeFrom,'0000-00-0')='0000-00-0',curdate(),activeFrom))
                     and (if(ifnull(activeTill,'0000-00-0')='0000-00-0',now(),activeTill))
Scroll to Top