Returning The Greater of Two Date Fields, Where Either Could Be NULL
In keeping with SQL tricks I wrote about a few days ago, here’s another technique that I’ve found useful recently.
Let’s say you want to return the greater of two date fields in your result set. If you know that both fields will contain a date, then you can simply use the GREATEST function.
GREATEST will return the largest of your argument. However, if either field could be
GREATEST won’t work – it will return
NULL if any of the arguments passed to it are
I recently needed a query to return the greater of two date fields, but either could be
NULL. If one was
NULL, I wanted to return the non-
NULL field. And if both were
NULL, that’s the only time I wanted to return
NULL. I was able to accomplish this by combining
GREATEST with COALESCE and NULLIF, and the idea of a zero date.
COALESCE function returns the first non-
NULL argument passed to it. I could use this to provide a default date if one of my fields was
NULL, like this.
The default date I passed as my second argument is the zero date. In MySQL, the zero date is defined as ‘0000-00-00’. And even though this date is not a valid date, it can be used in date comparisons, which is all I need it for in this case.
At this point, I can use
COALESCE to default any
NULL values to the zero date, and then use
GREATEST to get the greater of my two date fields.
GREATEST( COALESCE(date1, '0000-00-00'), COALESCE(date2, '0000-00-00') )
But if both fields are zero dates, then
GREATEST will return the zero date, which is invalid and can’t be saved. This is where I turn to
NULLIF function takes two arguments. If the first argument equals the second, it will return
NULL then following will return
NULLIF(COALESCE(date, '0000-00-00'), '0000-00-00')
Now, I can combine all these techniques into one evaluation. Working from the inside out, if either of my date fields is
NULL, return the zero date. Then compare the two and return the greatest date. Then, if the returned date happens to be the zero date, then return
NULLIF( GREATEST( COALESCE(date1, '0000-00-00'), COALESCE(date2, '0000-00-00') ), '0000-00-00' ) AS date
Need help building or maintaining a Rails app?
Jeremy has availability for new projects starting Q3 or Q4 of 2022.