CODEWITHSUNDEEP
sqloracle-database
M McCullum
M McCullum

Reputation: 1

Oracle date comparison in where clause issue

I am seeing different results for a query whenever I rearrange the conditions of the where clause; I am trying to figure out why this is. I was given to try and figure out what is going on, so I could use some help to ascertain what may be causing an issue. To be clear, we are not receiving errors when running the select statement, but receiving unintended data results. This is an Oracle database (10-g I believe). We have two conditions for the same field. The first is to make sure the results are greater than 30 years ago (this database was not set my by myself, but the team that owns the application decided to change the created_date to 30 in the past was a good idea).....the second is to have a more specific date range of September 1st to October 1st.

SELECT created_date
FROM tableName
WHERE 
created_date > sysdate - 11000 -- 30 years in the past
AND short_name like 'FOO%'
AND trunc(created_date) BETWEEN TO_DATE('09-01-2017', 'MM-DD-YYYY')  
AND TO_DATE('10-01-2017', 'MM-DD-YYYY')

When running the query above, we receive results with a created_date outside of the condition at the end of the where clause.

However, when I place the comparison between the created_date and the sysdate AFTER the comparison between the two dates, we receive the correct results.

SELECT created_date
FROM tableName
WHERE 
short_name like 'FOO%'
AND trunc(created_date) BETWEEN TO_DATE('09-01-2017', 'MM-DD-YYYY')  
AND TO_DATE('10-01-2017', 'MM-DD-YYYY')
AND created_date > sysdate - 11000 -- 30 years in the past

Also, I removed the trunc portion of "AND trunc(created_date) BETWEEN TO_DATE('09-01-2017', 'MM-DD-YYYY') " to "AND created_date BETWEEN TO_DATE('09-01-2017', 'MM-DD-YYYY') " and received the intended results with the sysdate comparison before or afterward.

SELECT created_date
    FROM tableName
    WHERE 
    created_date > sysdate - 11000 -- 30 years in the past
    AND short_name like 'FOO%'
    AND created_date BETWEEN TO_DATE('09-01-2017', 'MM-DD-YYYY')  
    AND TO_DATE('10-01-2017', 'MM-DD-YYYY')

EDIT:

  1. There are numerous records in the database. There is only 1 result with a created_date of 22-Sep-17. 

    SELECT created_date
FROM tableName
WHERE 
created_date > sysdate - 11000 -- 30 years in the past
AND short_name like 'FOO%'
AND trunc(created_date) BETWEEN TO_DATE('09-01-2017', 'MM-DD-YYYY')  
AND TO_DATE('10-01-2017', 'MM-DD-YYYY')

Produces 25 entries with a created_date of 17-Feb-04 and 1 with 22-Sep-17.

  1. I understand sysdate - 11,000 is a little more than 30 years. The current requirement is to do a check for 11000 days and not 30 years. I was off in the year estimate. The sysdate is used because records that are considered old or updated will be placed with a created_date further than sysdate - 11000. I understand this makes no sense when there is already another date range condition in this query, but the solution has already been built and sent off. In order to make changes, I need to figure out WHY I am getting what I am getting, which is why I am posting here.
  2. The second date range, in this case, 09-01-2017 and 10-01-2017, are parameters passed in by so they could be any range from any day in the past up until today. I understand it is a bad design, but I did not build it.

These will give two subtly different results for the values between 2017-10-01 00:00:01 and 2017-10-01 23:59:59. The query using TRUNC will include that range where without TRUNC that range will be excluded.

The intended result is to have on record returned back within the date range 22-Sep-17; however, we are receiving 25 records all with dates of 17-Feb-04....If I remove the created_date > sysdate - 11000 I receive the only one record as well.

Upvotes: 0

Views: 5463

Answers (1)

MT0
MT0

Reputation: 167962

The first is to make sure the results are greater than 30 years ago

You aren't subtracting 30 years, you are subtracting 11,000 days which is approximately (depending on leap years) 30 years and 42 days.

If you want to subtract 30 years then use:

ADD_MONTHS( date_value, -30 * 12 )

the second is to have a more specific date range of September 1st to October 1st.

This makes the first condition irrelevant - if it is between 2017-09-01 and 2017-10-01 then it will be within the last 30 years and you do not need that first condition.

I removed the trunc portion of "AND trunc(created_date) BETWEEN TO_DATE('09-01-2017', 'MM-DD-YYYY')" to "AND created_date BETWEEN TO_DATE('09-01-2017', 'MM-DD-YYYY')" and received the intended results with the sysdate comparison before or afterward.

These will give two subtly different results for the values between 2017-10-01 00:00:01 and 2017-10-01 23:59:59. The query using TRUNC will include that range where without TRUNC that range will be excluded.

Also, using TRUNC means that Oracle will not us an index on the created_date column (and would require a function-based index on TRUNC( created_date )). If you want to use an index then just use the column and test to see if it is greater or equal than the start of the range and less than the end of the range plus one day (to include up to 23:59:59 of that day).

The simplified equivalent of your TRUNC query is:

SELECT created_date
FROM   tableName
WHERE  short_name like 'FOO%'
AND    created_date >= DATE '2017-09-01'                    -- range start
AND    created_date <  DATE '2017-10-01' + INTERVAL '1' DAY -- range end + 1 day

Upvotes: 1

Related Questions