comparing dates
Hi,
An existing accounts_receivable report retrieves records according to different parameters and works fine. The report will begin running daily with a scheduler such that it will always retrieve yesterday's records, and a similar report which will retrieve previous records - not just yesterday's – according to the creation_date of the transaction (and also the creation_date of the receipts).
In other words, for one report in the scheduler I must retrieve records where the creation date = sysdate - 1. For the other I must retrieve records where the creation_date < sysdate - 1. If run on demand by a user, then instead of sysdate, it will be the date entered by the user.
Therefore, the parameter is based on a decode function using either sysdate - 1 or the entered date - 1. My calculation:
DECODE(:TransactionCreatedDate,'sysdate',TRUNC(SYSDATE)-1,TRUNC(TO_DATE(:TransactionCreatedDate,'dd/mm/yyyy'))-1)
This calculation must be the basis of my problem because I am having trouble comparing the creation_date to the parameter since they are different formats.
In the first report I have no problem because I just used the to_char function and compared the two fields to see if they are equal.
The second is problematic because I must check if one field is less than another and the two fields are different formats. I have played around with the to_char and to_date functions to no avail.
How can I compare the two dates? I have tried so many different combinations of to_date, to_char and am getting nowhere.
I would appreciate any help.
Thanks.
Leah