Menu
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?
994
Solution is simple: Add a nvl around each of your values and replace it with the value that suits your specific needs. Simple thing to remember: each function will return NULL when one of the parameters is NULL.
MAX ignores any null values. MAX returns NULL when there is no row to select. For character columns, MAX finds the highest value in the collating sequence.
By default, a column can hold NULL values. The NOT NULL constraint enforces a column to NOT accept NULL values. This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.
A field with a NULL value is a field with no value. If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.
COALESCE(GREATEST(date_one, date_two), date_one, date_two)
184
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
