Dave Dave - 3 months ago 9
MySQL Question

Having trouble using CASE to translate NULLs in MySQL

I"m using MySQL 5.5.37. I have this in a select statement ...

select (CASE s.end_date WHEN NULL THEN NOW() ELSE s.end_date END)


however, what is being output when the column in quesiton is null is NULL. I would expect today's date to be output. WHat is the proper way to translate a NULL column in a CASE statement to today's date?

Answer

Your syntax is close, but a bit off. This is what you were trying to do:

SELECT CASE WHEN s.end_date IS NULL THEN NOW() ELSE s.end_date END
FROM yourTable

But an easier and more concise way to handle possible NULL values in your end_date column would be to use the COALESCE function:

SELECT COALESCE(s.end_date, NOW())
FROM yourTable

From the documentation for COALESCE:

Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

In terms of your query, COALESCE will return end_date if it isn't NULL, and it will return NOW() if it is NULL.