Oracle second date range comparison ignored in where clause 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 &gt; 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) &gt; 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.
Not enough information, so I will just leave this here:

Bug 14073795  Wrong results on select statement with TRUNC(date) or ROUND(date)

Bug is fixed in 11.2.0.4.