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?
Advertisement
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')