Date Conversion Issues ORACLE SQL

this is going to be a totally newb question but my google-fu isn’t landing me anything timely so I figured I would ask you

Trying to do this query:

SELECT *
FROM SYSADM.PS_ADM_APPL_PROG
WHERE SCC_ROW_ADD_DTTM>=CAST(TO_CHAR(COALESCE('11-30-2019', '11-30-2019')) AS TIMESTAMP) 
OR SCC_ROW_UPD_DTTM>CAST(TO_CHAR(COALESCE('11-30-2020', '11-30-2020')) AS TIMESTAMP) 

But the predicate column is formatted as such:

04-OCT-12 06.21.14.553375000 AM

I’m guessing my TO_CHAR and CAST isn’t sufficient due to the error:

ORA-01843: not a valid month
01843. 00000 -  "not a valid month"

Anyone happen to know off the top of their head how to get past this?

Answer

Use a DATE constant:

WHERE SCC_ROW_ADD_DTTM >= DATE '2019-11-30' OR
      SCC_ROW_UPD_DTTM > DATE '2019-11-30'

You don’t have a time component, so a date literal seems sufficient.

If you have variables, you can use COALESCE():

WHERE SCC_ROW_ADD_DTTM >= COALESCE(:datevar, DATE '2019-11-30') OR
      SCC_ROW_UPD_DTTM > COALESCE(:datevar, DATE '2019-11-30')