I have the query below and am receiving an unexpected dataset returned back. I was wondering if you all might be able to explain what is going on for me? I am using Oracle 11g.
I understand having the sysdate check and the date range condition of the where clause is redundant, but as for now, I do cannot change the query without having a rationalization as to why we are receiving the data we are receiving. When I run the query below, I receive 25 records with a created_date of 17-Feb-04 and only 1 with 22-Sep-17. There are many more records in this database with other dates, but for some reason, this is what we are receiving.
SELECT created_date
FROM tableName LOAN
WHERE
loan.created_date > sysdate - 11000
AND loan.SYSTEM_ID ='ALS'
AND loan.BANK_NUM = '021'
AND loan.short_name like 'NAME%'
AND trunc(loan.CREATED_DATE) BETWEEN TO_DATE('09-01-2017', 'MM-DD-YYYY')
AND TO_DATE('10-13-2017', 'MM-DD-YYYY')
If I place sysdate at the end of the query, I receive the expected results (1 transaction with created_date of 22-Sep-17). If I trunc(loan.created+date) > sysdate - 11000 and leave everything else the same, I receive the correct expected result. If I remove the trunc from AND trunc(loan.CREATED_DATE) BETWEEN TO_DATE('09-01-2017', 'MM-DD-YYYY') AND TO_DATE('10-13-2017', 'MM-DD-YYYY') to AND loan.CREATED_DATE BETWEEN TO_DATE('09-01-2017', 'MM-DD-YYYY') AND TO_DATE('10-13-2017', 'MM-DD-YYYY'), I receive the expected results. However, I do not understand why those configurations of the same where clause (or slightly different) produce those results.
I understand that when trunc is used on a date field, it essentially rounds up to the beginning of that date. Therefore, I may see a difference in results when comparing created_date field against sysdate due to the timestamps of those fields, but the results I am seeing do not vary by minutes or hours, but years. Somehow, the second date condition is either being ignored or there is an issue with how I am comparing the sysdate and the date comparison.
