How to compare mySQL dates
Alberto last edited by
There is a table in which, among other things, two fields
activeFrom [date] DEFAULT NULL activeTill [date] DEFAULT NULL
Data are entered in the table from different sources, so the fields have different meanings:
0000-00-00and some date (e.g.)
SQL request to remove all lines where the current date falls between
In the field.
true. (i.e., now 2015-12-16, in the table
activeFrom = NULLand
activeTill = '2016-01-01'I need a record.
activeTill- Date inclusive
In principle, I did a comparison of all 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 we can solve something more compelling. Without this terrible transfer.
Can someone offer 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))