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

From the little i got your question you want one of this

A] date_end more than current date or if it is null

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

B] date_end must be present and more than current date

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

Answer 2

You could try:

SELECT * FROM pf_news WHERE date_start <= CURRENT_DATE() AND 
(date_end <= CURRENT_DATE() OR date_end IS NULL)

or some similar logical grouping.

Answer 3

or try

SELECT * FROM pf_news WHERE date_start <= now() AND (date_end >= now() OR date_end = 0)

0 seems to work just fine for me

Answer 4

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! :)