Filtering of records between sysdate and sysdate+7 from Oracle Sql is not working correctly

I am firing an SQL query to filter records between sysdate and sysdate+7 but I am getting records outside the range as well. What is wrong my SQL

cursor.execute("""
select
    'Shipment' as object_type
    , trunc(sc.effective_timestamp) reference_date
    , sc.location_name location
  from
    master.cons_search  c
    inner orbit.status_cons sc  ON (c.tms_cons_id=sc.cons_id)

  where
    1=1
    AND  c.global_company IN ('SWEET234')
    AND sc.type = '1201'
    and (trunc(c.ets) >= trunc(sysdate) and trunc(c.ets) <= (trunc(sysdate) + 7))
    """)

data=cursor.fetchall()

I even tried a between function

and trunc(c.ets) between trunc(sysdate) and  (trunc(sysdate) + 7)

But all of them giving results outside the range . What is the issue here?

Answer

You are filtering on c.ets.

You are selecting sc.effective_timestamp.

I suspect that you are confused about the dates. If you filter on the same column you are selecting, then you should not see out-of-range dates.

Advertisement

Answer