Search Last 7 days excluding today Oracle SQL-CodePudding

I have the below code to which I want to return the last 7 days excluding today (for example from 5th May - 11th May as opposed to 5th May - 12th May)

What else would I be able to include to acheive this?

    SELECT * 
FROM TABLE_1
WHERE DATE_TIME >= SYSDATE -7

CodePudding user response：

You want to have a range that starts from 7 days before midnight today and ends before midnight today:

SELECT *
FROM   table_name
WHERE  date_time >= TRUNC(sysdate) - 7
AND    date_time <  TRUNC(sysdate);

CodePudding user response：

This should work:

    SELECT * 
FROM TABLE_1
WHERE DATE_TIME >= SYSDATE -7
  AND TRUNC(DATE_TIME) != TRUNC(SYSDATE)

The TRUNC is needed to strip the time portion of the date column and sysdate.

Note that DATE_TIME >= SYSDATE -7 will include the time portion of SYSDATE and substract 7 days. If you run the query at 10AM, do you want to include rows that have date_time = sysdate - 7 at 9AM too ? If so it is better to add a TRUNC there too DATE_TIME >= TRUNC(SYSDATE) -7.