When I run the following query I got an error how to use trunc date() function Sql Oracle

Question

help me on mentioned code When I run the following query I got an error. Can Someone help me to solve this.

Select 
     R_NUMBER
    ,CATEGORY
    ,DONOR_OP
    ,to_char(to_date(datetime_Table,'DD/MM/YYYY HH24:MI:SS'),'MM/DD/YYYY')
FROM EDW_TRANSACTION_BASE 
WHERE circle_id = '116'
AND to_char(to_date(datetime_Table,'DD/MM/YYYY HH24:MI:SS'),'MM/DD/YYYY') = TRUNC(SYSDATE-1)
AND trunc(REPORT_DATE) = TRUNC(SYSDATE);

In my data date format is DD/MM/YYYY HH24:MI: SS and system date is 2/22/2022

Error message:

Not a valid month

how may I scheduled without changing date in oracle sql

Answer 1

If you are comparing dates you don't need the TO_CHAR() here:

to_char(to_date(datetime_Table,'DD/MM/YYYY HH24:MI:SS'),'MM/DD/YYYY') = TRUNC(SYSDATE-1)

change it to :

TRUNC(to_date(datetime_Table,'DD/MM/YYYY HH24:MI:SS')) = TRUNC(SYSDATE-1)

in the where clause. (your TRUNC(SYSDATE-1) will give you the date without time string and I suppose you want to trunc the results from the datetime_Table too since they have time string included and the matching will not work properly if you're loading full time strings, but it's up to your data, I leave this part to you)