Search code examples
sqloracle-databaseoracle12ctoad

Oracle SQL get records between Previous day and Today within the specified duration

How can i fetch all data between Product dates exactly from previous day 9:01AM to till today 9:00 AM (any timezone)? Below query not giving the data exactly between the time durations. This is dynamic query. Everyday it will be executed.

SELECT * from Table1 where product_date > (sysdate-1) - interval '09' hour  and product_date < sysdate - interval '09' hour ;
Solution

  • You are basically correct. You just need to truncate the date:

    where product_date > trunc(sysdate) - interval '15' hour and
      product_date < trunc(sysdate) + interval '09' hour ;

    sysdate has a time component as well as the date.