Search code examples
sqloracledate-arithmetic

Oracle SQL - Data from previous week (Sunday to Saturday) regardless of when it's run

I'm looking to return data in Oracle for the last full week starting Sunday and finishing Saturday. This needs to be able to run any day of the week.

So I know that this is possible in SQL Server as I have reports that do the exact same thing:-

SET @startdate = DATEADD(wk, -1, DATEADD(wk, DATEDIFF(wk, 0,getdate()), -1))
SET @enddate = DATEADD(wk, DATEDIFF(wk, 0, getdate()), -1)

Today being Friday 17th March the above would return data between Sunday 5th March and Saturday 11th March.

I want to do the same thing in Oracle. Everywhere I've looked so far comes back with results like this:-

SELECT TRUNC (SYSDATE) - (SELECT TO_CHAR (SYSDATE, 'D') FROM DUAL),
       TRUNC (SYSDATE) - (SELECT TO_CHAR (SYSDATE, 'D') + 1 FROM DUAL)
FROM DUAL

Or

SELECT SYSDATE AS TODAYS_DATE, 
       NEXT_DAY (SYSDATE - 7, 'SAT') AS PREV_SATURDAY,
       NEXT_DAY (SYSDATE - 7, 'SUN') AS PREV_SUNDAY
FROM DUAL

I'm trying to avoid any 'sysdate-7' type code since that's pretty unwieldy in this situation - can anyone help at all?

Thanks

Solution

  • If, at any given point in time, "previous week" means the seven-day period that ENDS on the most recent midnight at the beginning of a Sunday, then something like this should work:

    with inputs (dt) as (
       select sysdate     from dual union all
       select sysdate + 1 from dual union all
       select sysdate + 2 from dual union all
       select sysdate + 3 from dual
     )
-- end of test data; SQL solution begins below this line
select to_char(dt, 'Dy dd-Mon-yyyy hh:mi AM') as dt,
       trunc(dt + 1, 'iw') - 8 as prev_wk_start,
       trunc(dt + 1, 'iw') - 1 as prev_wk_end
from   inputs;

DT                         PREV_WK_START         PREV_WK_END
------------------------   -------------------   -------------------
Fri 17-Mar-2017 10:58 AM   03/05/2017 00:00:00   03/12/2017 00:00:00
Sat 18-Mar-2017 10:58 AM   03/05/2017 00:00:00   03/12/2017 00:00:00
Sun 19-Mar-2017 10:58 AM   03/12/2017 00:00:00   03/19/2017 00:00:00
Mon 20-Mar-2017 10:58 AM   03/12/2017 00:00:00   03/19/2017 00:00:00

    Note: Whenever we work with time intervals, we must decide if the endpoints are included. In most cases, the best (and most used) convention is that the start date/time is included, while the end date/time is NOT included. The query above is consistent with this interpretation. If the query is run for an input like date '2017-03-19', which is midnight at the beginning of a Sunday, the query will return the week that ENDS exactly at that date and time. All of this "previous week" strictly precedes the input date/time, because the end point of the week is NOT included in the "one-week interval."