MYSQL: comparing a NULL date to CURRENT_DATE

Question

I have a MYSQL table with news articles with the fields "date_start" and "date_end" to indicate which news to show on a web site. Articles are public if date_start is before today and the date_end hasn't already gone by (after today).

The problem: I'd like to let the admin leave date_end NULL if the article is of permanent nature and doesn't expire. This of course doesn't work with my select:

 SELECT * FROM pf_news WHERE date_start <= CURRENT_DATE() AND date_end >= CURRENT_DATE()

It leaves out articles with a NULL date_end. I tried playing a bit with IF statements but it got confusing for me. Is there a simple way of doing this, or should I just set date_end to 3000-01-01 if it's left empty? :)

Answer 1

Your last proposal sounds correct. You can use the IFNULL function. It has two arguments. If the first argument is non-null, it returns the first argument. The second argument is what to return if the first argument is null. So in your statement, you can say this:

SELECT * FROM pf_news WHERE date_start <= CURRENT_DATE() AND IFNULL(date_end, '3000-01-01') >= CURRENT_DATE()

This can also be used in SQL Server, but they call it the "ISNULL" function.

Just set a calendar reminder for yourself on 12/31/2999 to change your code! :)