0

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.

  • 1
    You've got a possible answer, in that there was a known bug with Oracle 11 prior to 11.2.0.4. What specific version of Oracle 11g are you running? Also - it's not at all clear which versions of the query generate the right result, or exactly what the wrong result is - if the patch isn't your answer, you should clear that up. – RDFozz Nov 14 '17 at 19:35
  • 1
    dba.stackexchange.com/help/merging-accounts @MMcCullum – Michael Green Nov 14 '17 at 22:11
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.