Using NVL with TO_DATE and TO_CHAR functions resulting in ORA-01843 error

I have the below query that when I add the NVL function, I am getting an error

ORA-01843: not a valid month

I simply want to use the end date range as the current date (SYSDATE) if the parameter :Transaction_DateTO is NULL.

SELECT *
FROM MyTable TXN
WHERE 
    TXN.TRANSACTION_DATE BETWEEN
       to_date(to_char(:Transaction_DateFROM,'YYYY-MM-DD') || ' 00:00:00', 'YYYY-MM-DD HH24:Mi:SS')  
       AND 
       NVL(to_date(to_char(:Transaction_DateTO,'YYYY-MM-DD') || ' 23:59:59', 'YYYY-MM-DD HH24:Mi:SS'), SYSDATE)  

You need to sign in to view this answers