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 NULL
, then GREATEST
won’t work – it will return NULL
if any of the arguments passed to it are NULL
.
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.
The 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.
COALESCE(date, '0000-00-00')
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
. The NULLIF
function takes two arguments. If the first argument equals the second, it will return NULL
. If date
if NULL
then following will return NULL
:
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 NULL
.
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 is currently booked until mid-2023, but always happy to chat.