Finding max of nullable date between two columns in db2

Question

I have a table in Db2 called myTable.

It has several columns:

a |  b    | date1        |  date2
---------------------------------------------
1    abc     <null>        2014-09-02
2    aax     2015-12-30    2016-09-02
2    bax     2015-10-20    <null>
2    ayx     2014-12-10    2016-02-12

As seen from values above, date1 and date2 can have null values as well.

How can I get the max of both date1 and date2 together ?

i.e. the output of the query should be 2016-09-02 as that is the max date of all the dates present in date1 and date2.

I am using Db2-9.

Thanks for reading!

Answer 1

How about using a UNION query:

SELECT MAX(t.newDate)
FROM
(
    SELECT date1 AS newDate
    FROM myTable
    UNION
    SELECT date2 AS newDate
    FROM myTable
) t

Another option:

SELECT CASE WHEN t.date1 > t.date2 THEN t.date1 ELSE t.date2 END
FROM
(
    SELECT (SELECT MAX(date1) FROM myTable) AS date1,
           (SELECT MAX(date2) FROM myTable) AS date2
    FROM SYSIBM.SYSDUMMY1
) t