Taking the Max date from three date columns

Question

I need to take the max date of three columns by ID: Date Created, Updated Date, and Transaction Date. Below is a simplified version of how the data I refer to looks like and is not representative of the actual data I am working with.

+--------------------------------------------------------+
| ID |  Date Created   | Updated Date | Transaction Date |
+--------------------------------------------------------+
| 12 |   19-APR-19     |     NULL     |     20-APR-19    |
| 68 |   02-Nov-18     |   03-DEC-18  |     02-NOV-18    |
| 12 |   05-MAY-19     |   12-MAY-19  |     12-MAY-19    |
| 72 |   10-SEP-19     |   01-OCT-19  |     25-SEP-19    |
+--------------------------------------------------------+

After retrieving the max of these columns per ID, I should get:

+---------------------+
| ID |      Date      | 
+---------------------+
| 68 |   03-DEC-18    |   
| 12 |   12-MAY-19    |     
| 72 |   01-OCT-19    |   
+---------------------+

So far, I have tried a variation of the following:

   CASE WHEN
      TO_CHAR(ud.updated_date,'MM/DD/YYYY') >= TO_CHAR(NVL(mqp.trans_effective_date,0),'MM/DD/YYYY') AND TO_CHAR(ud.updated_date,'MM/DD/YYYY') >= TO_CHAR(NVL(mt.date_created,0),'MM/DD/YYYY') THEN TO_CHAR(ud.updated_date,'MM/DD/YYYY')
      WHEN 
      TO_CHAR(mt.transaction_date,'MM/DD/YYYY') >= TO_CHAR(NVL(ud.updated_date,0),'MM/DD/YYYY') AND TO_CHAR(mt.transaction_date,'MM/DD/YYYY') >= TO_CHAR(NVL(mt.date_created,0),'MM/DD/YYYY') THEN TO_CHAR(mt.transaction_date,'MM/DD/YYYY')
      WHEN
      TO_CHAR(mt.date_created,'MM/DD/YYYY') >= TO_CHAR(NVL(ud.updated_date,0),'MM/DD/YYYY') AND TO_CHAR(mt.date_created,'MM/DD/YYYY') >= TO_CHAR(NVL(mt.transaction_date,0),'MM/DD/YYYY') THEN TO_CHAR(mt.date_created,'MM/DD/YYYY')
  END Date

But this ends in the following error:

ORA-00932: inconsistent datatypes: expected NLS PARAMETER got DATE
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:

I have also tried different variations of:

  GREATEST(TO_CHAR(mt.date_created,ud.updated_date,mt.transaction_date), 'MM/DD/YYYY') Date

But then I get this error:

ORA-00932: inconsistent datatypes: expected NLS PARAMETER got DATE
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action

Any advice would be greatly appreciated!

Answer 1

Use greatest():

select id, greatest(updated_date, mqp.trans_effective_date, mt.date_created)
from . . .

Assuming all are of type date (as implied by your question), you should have no conversion issues.

If you have ids on multiple rows, then use aggregation:

select id, greatest(max(updated_date), max(mqp.trans_effective_date), max(mt.date_created))
from . . .