Comparing dates sql
I have written some code and this is working as expected. However client has now asked that I extract all invoices <= 2016/03/31 but the invoice received date OR the invoice match date must that > 2016/03/31
I have extended the code in the where clause as follows:
and to_char(aia.invoice_date, 'YYYY/MM/DD') <= :p_date
and (to_char(aia.creation_date, 'YYYY/MM/DD') > :p_date or to_char(aila.creation_date, 'YYYY/MM/DD') > :p_date)
Although most of the records extracted are correct there are many others that should not have extracted. In looking at these records I noticed that all the incorrect ones relate to where the invoice match dates are null.