sqloracle-databasedatetime

Oracle SQL - Select oldest of 3 dates, but ignore NULLs

I have this Oracle SQL statement to get the oldest of 3 dates:

SELECT LEAST(DATE_1,DATE_2,DATE_3) FROM MYTABLE

However, if one of the dates is NULL, the entire LEAST function will return NULL, regardless of whether the the other dates are not NULL. I would like to get the oldest of the three dates, but ignore any NULL dates, so that the only time LEAST would return NULL is if all three dates are NULL.

I also cannot have the function return incorrect dates, so this will not work:

SELECT LEAST(NVL(DATE_1,SYSDATE),NVL(DATE_2,SYSDATE),NVL(DATE_3,SYSDATE)) FROM MYTABLE

What's the cleanest way to do this?

Solution

  • Here is one way:

    SELECT LEAST(COALESCE(DATE_1, DATE_2, DATE_3),
             COALESCE(DATE_2, DATE_1, DATE_3),
             COALESCE(DATE_3, DATE_2, DATE_1)
            )
FROM MYTABLE