Bring data from the last day of the previous month beyond-CodePudding

I'm using Oracle SQL Developer and I would like to make a SELECT that brings all the products sold from the last day of the previous month (but only products sold from 4 pm on the last day of the previous month) to the current day (only until 8 am of the current day).

For example, today is 7/21/2022. If I run this query today, it should bring data from:

06/30/2022 above 16:00hrs -> 07/21/2022 until 08:00hrs

CodePudding user response：

You can use TRUNC(value, 'MM') to find midnight of the 1st day of the current month and then subtract 8 hours to find the start of the range and then use TRUNC(value) to find midnight of today and add 8 hours to find the end of the range:

SELECT *
FROM   table_name
WHERE  date_column >= TRUNC(SYSDATE, 'MM') - INTERVAL '8' HOUR
AND    date_column <= TRUNC(SYSDATE)   INTERVAL '8' HOUR;

CodePudding user response：

You can use TRUNC to get to the first day of a date's month. Then subtract one day and add sixteen hours. And it's again TRUNC that you use to get back to the beginning of a day (midnight) to which you can add eight hours.

select * 
from mytable
where dt >= trunc(sysdate, 'mm') - interval '1' day   interval '16' hour
  and dt < trunc(sysdate, 'dd')   interval '8' hour
order by dt;