1

I have two DATE-fields (PublishFrom, PublishTo) that are nullable. I am trying to get the records with this query:

SELECT * FROM tblNews 
    WHERE Publish = 1 
    AND IFNULL(PublishFrom, CURDATE() - INTERVAL 1 DAY) <= "2015-03-13" 
    AND IFNULL(PublishTo, CURDATE() + INTERVAL 1 DAY) >= "2015-03-13" 
    ORDER BY DateCreated DESC'

The meaning is that if the value of (one of) these fields is null, it is replaced by the value of yesterday and/off tomorrow so that the entry is always shown.

Right now I see only the posts where PublishFrom and/or PublishTo is not null. How come?

bflydesign
  • 483
  • 8
  • 22

2 Answers2

2

Using CURDATE() + 1 or - 1 will convert CURDATE and screw up the comparison. Instead use + INTERVAL 1 DAY (or - INTERVAL 1 DAY)

Explosion Pills
  • 188,624
  • 52
  • 326
  • 405
  • this works but does not give me the solution to the problem where only records are shown where PublishFrom and/or PublishTo is not null – bflydesign Mar 13 '15 at 20:43
  • `SELECT IFNULL(NULL, CURDATE() + INTERVAL 1 DAY) >= "2015-03-13" AND IFNULL(NULL, CURDATE() - INTERVAL 1 DAY) <= "2015-03-13"` returns `TRUE` so maybe `Publish` does not equal `1`? OR maybe I just don't understand your question – Explosion Pills Mar 13 '15 at 20:46
  • Publish does equal 1 for all records. For some reason with this query the records where PublishFrom AND PublishTo are null, are not selected – bflydesign Mar 13 '15 at 20:58
  • on sqlfiddle it works perfectly, on my own NAS-server and on my online domain I get an empty result... – bflydesign Mar 14 '15 at 19:45
  • 1
    @BFlyDesign could have to do with the MySQL version -- make sure they are the same. – Explosion Pills Mar 14 '15 at 19:47
0

Why not just use explicit logic?

SELECT *
FROM tblNews 
WHERE Publish = 1  AND
     (PublishFrom <= '2015-03-13' OR PublishFrom IS NULL) AND
     (PublishTo >= '2015-03-13' OR PublishTo IS NULL)
ORDER BY DateCreated DESC
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786