MYSQL: comparing NULL dates with CURRENT_DATE

Question

I have a MYSQL news table with the fields "date_start" and "date_end" to indicate which news to display on the website. Articles are publicly available if date_start is up to today and date_end has not passed yet (after today).

Problem: I want the administrator to leave date_end NULL if the article is persistent and does not expire. This, of course, does not work with my choice:

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

It leaves articles with NULL date_end . I tried to play around with IF instructions a bit, but it became difficult for me. Is there an easy way to do this, or should I just set date_end to 3000-01-01 if it is left blank? :)

Answer 1

You can 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 2

Your last sentence sounds correct. You can use the IFNULL function. It has two arguments. If the first argument is not null, it returns the first argument. The second argument is what needs to be returned if the first argument is null. Therefore, in your statement you can say the following:

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

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

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

Answer 3

From a small I got your question, do you want one of this

A] date_end is greater than the current date or if it is empty

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

B] date_end must be present and greater than the current date

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

Answer 4

or try

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

0 seems to work fine for me

Answer 5

Another possibility is to use COALESCE, it returns the first nonzero value of the list:

 SELECT * FROM pf_news WHERE date_start <= CURRENT_DATE() AND COALESCE(date_end,'9999-1-1') >= CURRENT_DATE() 

Thus, if date_end is NULL, the future date '9999-1-1' will be used inside the comparison. But it will break after this date :-)