SQL query not fetching correct number of records

I have this SQL query. When I specify the date and time explicitly, it shows the correct number of rows:

select *
from annual_report_order
where report_order_date >= '29-OCT-2024 00:00:00.000'
  and report_order_date <= '02-NOV-2024 23:59:59.000'

However, when I use sysdate, output shows fewer rows:

select *
from annual_report_order
where report_order_date >= sysdate - 5
  and report_order_date <= sysdate - 1

Column report_order_date is defined as TIMESTAMP(6).

My aim is to automate this and use a scheduler, and run this SQL query every Sunday with report_order_date condition from Monday 00:00 till Saturday 23:59:59.

Could you please advise why sysdate shows fewer rows?

Use TO_DATE function

Sysdate is keeping timestamp, means it stores current minute, hour and seconds with date. It is creating the problem in keeping range of timespan in your resultset. The following SQL statement is showing the problem and solution.

SELECT TO_CHAR(SYSDATE-2,'DD-MON-YYYY HH24 MI SS') stamp, TO_CHAR(TO_DATE(SYSDATE,'DD-MON-YYYY')-2,'DD-MON-YYYY HH24 MI SS') AS OnlyDate FROM DUAL;

In the first column Stamp date with hour, minute and second. but in second column it is with OnlyDate. builtin TO_DATE function with date format makes the difference. There is a problem with TO_DATE function on DATE data type, it returns only last two digits of year of date. But, with string type data of date value it works properly. The followings tricks used to solve the issue.

TO_CHAR(TO_DATE(to_char(SYSDATE,'DD-MON-YYYY'),'DD-MM-YYYY'),'DD-MON-YYYY') AS OnlyDate

In your case the solution is

select *
from annual_report_order
where report_order_date >= TO_CHAR(TO_DATE(to_char(SYSDATE,'DD-MON-YYYY'),'DD-MON-YYYY')-5,'DD-MON-YYYY')
  and report_order_date <=  TO_CHAR(TO_DATE(to_char(SYSDATE,'DD-MON-YYYY'),'DD-MON-YYYY')-1,'DD-MON-YYYY')

The solution is implemented on oracle xe 21c.

Thanks