views:

63

answers:

2

I need to update a row with a formula based on the largest value of two DATETIME columns. I would normally do this:

GREATEST(date_one, date_two)

However, both columns are allowed to be NULL. I need the greatest date even when the other is NULL (of course, I expect NULL when both are NULL) and GREATEST() returns NULL when one of the columns is NULL.

This seems to work:

GREATEST(COALESCE(date_one, date_two), COALESCE(date_two, date_one))

But I wonder... am I missing a more straightforward method?

+5  A: 

COALESCE(GREATEST(date_one, date_two), date_one, date_two)

Vadim K.
Nice! Thank you very much. I just hope I'll never need to generalize this for **n** columns... ;-P
Álvaro G. Vicario
A: 

That looks like the best solution to me. It's better than using NVL and a "magic" date because that would not return null if both values were null.

Jamie Ide