Finding Past Due Dates

[TapeGun007]

Not all items in my database have a due date.  I already have a page that lists everything that is due today and that's fine.

What I want to do is sort out all of the null dates "0000-00-00" but list the overdue items (past today) and not the current due dates (today's date).

This was my failed attempt:

$stmt = $pdo->prepare('SELECT * FROM Prospects WHERE UserID = ? AND FollowUpDate IS NOT NULL AND FollowUpDate < ?');
    $stmt->execute([$ID,$today]);

The above will list all the dates that are null anyway.  I can't seem to figure out how to write this correctly.

Thanks.

[TapeGun007]

mySQL Server Version 5.7.23

[TapeGun007]

Ah nvm, I got it unless there is a better way to write this, I would be interested.

 

SELECT * FROM Prospects WHERE UserID = ? AND FollowUpDate < ? AND FollowUpDate > '0000-00-00'

 

[requinix]

If you used actual NULLs and not 0000-00-00 dates then you wouldn't have to care about them. It's also better from a purist standpoint as a lack of data is what NULL is supposed to mean.

SELECT * FROM Prospects WHERE UserID = ? AND FollowUpDate < ?

Because comparing NULL with anything fails so those rows won't be matched.

[TapeGun007]

Ah, I probably got some bad information in my reading, but I was under the perception that when the date is set to 0000-00-00 it is considered a NULL.  This, of course, did not make sense to me.  In my testing just now, I see the NULL value when auto inserted by mySQL. 

Thanks.

[TapeGun007]

New question related to this.  So... when a new prospect is created, the database inserts an automatic value of null.  But then I set a follow up date and then when that date passes, I need to reset that value to a null.  

I simply put if $followupdate !isset then

$followupdate = null;

But when $followupdate is written to the database, it is now "0000-00-00" and I cannot seem to get the value back to null in mySQL.  Everything works fine the way I have it, but call me OCD.

[Barand]

UPDATE mytable SET thedate = NULL WHERE thedate = '0000-00-00';