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:
0000-00-00 and some date (for example)
The challenge :
SQL query to pull out all rows where the current date falls between
Moreover, if the field is
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] WHERE ( (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))