oracle sql get last Sunday and last Saturday

Question

I will calculate last Sunday and last Saturday on every Monday.

E.g. today is 08 July 2013 Monday

last Sunday: 30 June 2013 00:00:00

last Saturday: 6 July 2013 23:59:59.

Note the last Sunday is from 00:00:00 and last Saturday is until 23:59:59

Answer 1

This will be more useful. You can take Last Saturday or Sunday from any given day(Not only Monday) by using the query below.

select NEXT_DAY(sysdate-7, 'SATURDAY') AS last_saturday,
       NEXT_DAY(sysdate-7, 'SUNDAY') AS last_sunday
from dual;

Answer 2

Use the following PLSQL Function to get the DayDate of Week.

Usage of function to get Day of Month.

SELECT
 DayofMonth(to_date('2020-03-29T01:00Z', 'yyyy-mm-dd"T"hh24:mi"Z"'), 'FRI', 'LAST') LASTFRI    -- 27-MAR-2020 01:00:00
 ,DayofMonth(to_date('2020-03-29T01:00Z', 'yyyy-mm-dd"T"hh24:mi"Z"'), 'SUN', 'FIRST') FIRSTSUN -- 01-MAR-2020 01:00:00
 ,DayofMonth(to_date('2020-03-29T01:00Z', 'yyyy-mm-dd"T"hh24:mi"Z"'), 'SUN', 'THIRD') THIRDSUN -- 15-MAR-2020 01:00:00
 
 ,DayofMonth(to_date('2020-02-02T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"'), 'SUN', 'THIRD') THIRDSUN_FEB29  -- 16-FEB-2020 01:01:00
 ,DayofMonth(to_date('2021-02-02T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"'), 'SUN', 'FOURTH') FouthSUN_FEB28 -- 28-FEB-2021 01:01:00
from dual;

PLSQL-Function: Below function which identifies the Last Day of the Month, Converts to Number using TO_NUMBER() function to pass as a parameter to LAST_DAY() function.

create or replace FUNCTION DayofMonth 
(dateOfMonth in date, dayOfWeek in varchar2 default 'SUN', weekOfMonth in varchar2 default 'LAST')
return date AS date_out date;

firstDayOfMonth number(5); lastDayOfMonth number(5);
monthFirstDayDate date; monthSecondDayDate date;
monthThirdDayDate date; monthFourthDayDate date;
monthLastDayDate date;
BEGIN

  SELECT TO_NUMBER(to_char( trunc(dateOfMonth) - (to_number(to_char(dateOfMonth,'DD')) - 1), 'DD'), '9G999D99') into firstDayOfMonth FROM dual;
  SELECT TO_NUMBER(to_char(trunc( add_months(trunc(dateOfMonth) - (to_number(to_char(dateOfMonth,'DD')) - 1), 1) -1 ), 'DD'), '9G999D99') into lastDayOfMonth FROM dual;
DBMS_OUTPUT.put_line('firstDayOfMonth:' || firstDayOfMonth || ', lastDayOfMonth:' || lastDayOfMonth);

select next_day(LAST_DAY(dateOfMonth) - (lastDayOfMonth - (7 * 0)), dayOfWeek) into monthFirstDayDate   FROM DUAL;
select next_day(LAST_DAY(dateOfMonth) - (lastDayOfMonth - (7 * 1)), dayOfWeek) into monthSecondDayDate  FROM DUAL;
select next_day(LAST_DAY(dateOfMonth) - (lastDayOfMonth - (7 * 2)), dayOfWeek) into monthThirdDayDate   FROM DUAL;
select next_day(LAST_DAY(dateOfMonth) - (lastDayOfMonth - (7 * 3)), dayOfWeek) into monthFourthDayDate  FROM DUAL;
select next_day(LAST_DAY(dateOfMonth) - (7),                        dayOfWeek) into monthLastDayDate    FROM DUAL;

DBMS_OUTPUT.put_line('monthFirstDayDate:'  || monthFirstDayDate || ', monthSecondDayDate:' || monthSecondDayDate);
DBMS_OUTPUT.put_line('monthThirdDayDate:'  || monthThirdDayDate || ', monthFourthDayDate:' || monthFourthDayDate);
DBMS_OUTPUT.put_line('monthLastDayDate:'   || monthLastDayDate);

IF (weekOfMonth = 'LAST') then
  date_out := monthLastDayDate;
ELSIF (weekOfMonth = 'FIRST') then
  date_out := monthFirstDayDate;
ELSIF (weekOfMonth = 'SECOND') then
  date_out := monthSecondDayDate;
ELSIF (weekOfMonth = 'THIRD') then
  date_out := monthThirdDayDate;
ELSIF (weekOfMonth = 'FOURTH') then
  date_out := monthFourthDayDate;
ELSE
  date_out := null;
END IF;

    return date_out;
EXCEPTION
    when others then
        DBMS_OUTPUT.put_line('Date:' || dateOfMonth || ', Day:' || dayOfWeek || ', Week:' || weekOfMonth || ' / ' || sqlcode || ' / ' || SQLERRM(sqlcode));
        date_out := null;
        return date_out;
END;
/

---

First and Last Day of Month - Oracle SQL

SELECT
  to_char( trunc(sysdate) - (to_number(to_char(sysdate,'DD')) - 1), 'DD') as firstDay
  ,to_char(trunc( add_months(trunc(sysdate) - (to_number(to_char(sysdate,'DD')) - 1), 1) -1 ), 'DD') as lastDay
FROM dual;

Tests to check the Day of the Month with out any function:

select
    next_day(LAST_DAY(to_date('2021-01-29T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - 7, 'sun') as JAN_Month_Last_SUNDAY31 -- 31-JAN-2021 01:01:00
    ,next_day(LAST_DAY(to_date('2021-02-01T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - 7, 'sun') as FEB_Month_Last_SUNDAY28 -- 28-FEB-2021 01:01:00
    ,next_day(LAST_DAY(to_date('2021-04-01T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - 7, 'sun') as APRL_Month_Last_SUNDAY30 -- 25-APR-2021 01:01:00
    
    ,next_day(LAST_DAY(to_date('2021-01-29T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - 31, 'sun') as JAN_Month_First_SUNDAY31 -- 31-JAN-2021 01:01:00
    ,next_day(LAST_DAY(to_date('2021-02-01T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - 28, 'sun') as FEB_Month_First_SUNDAY28 -- 28-FEB-2021 01:01:00
    ,next_day(LAST_DAY(to_date('2021-04-01T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - 30, 'sun') as APRL_Month_First_SUNDAY30 -- 25-APR-2021 01:01:00
FROM DUAL;

select
  next_day(LAST_DAY(to_date('2021-01-29T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - 7, 'mon') as JAN_Month_Last_Mon31
  ,next_day(LAST_DAY(to_date('2021-01-29T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - 7, 'fri') as JAN_Month_Last_Fri31
  ,next_day(LAST_DAY(to_date('2021-01-29T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - (31 - (7 * 0)), 'fri') as JAN_Month_First_Fri31
  ,next_day(LAST_DAY(to_date('2021-01-29T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - (31 - (7 * 1)), 'fri') as JAN_Month_Second_Fri31
  ,next_day(LAST_DAY(to_date('2021-01-29T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - (31 - (7 * 2)), 'fri') as JAN_Month_Third_Fri31
  ,next_day(LAST_DAY(to_date('2021-01-29T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - (31 - (7 * 3)), 'fri') as JAN_Month_Fourth_Fri31
  ,next_day(LAST_DAY(to_date('2021-01-29T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - (31), 'fri') as JAN_Month_Last_Fri31
FROM DUAL;

select
  next_day(LAST_DAY(to_date('2021-02-02T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - (28 - (7 * 0)), 'fri') as FEB_Month_First_Fri28
  ,next_day(LAST_DAY(to_date('2021-02-02T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - (28 - (7 * 1)), 'fri') as FEB_Month_Second_Fri28
  ,next_day(LAST_DAY(to_date('2021-02-02T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - (28 - (7 * 2)), 'fri') as FEB_Month_Third_Fri28
  ,next_day(LAST_DAY(to_date('2021-02-02T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - (28 - (7 * 3)), 'SUN') as FEB_Month_Fourth_Fri28
  ,next_day(LAST_DAY(to_date('2021-02-02T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - (7), 'fri') as FEB_Month_Last_Fri28
FROM DUAL;

select
  next_day(LAST_DAY(to_date('2020-02-02T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - (29 - (7 * 0)), 'fri') as FEB_Month_First_Fri29
  ,next_day(LAST_DAY(to_date('2020-02-02T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - (29 - (7 * 1)), 'fri') as FEB_Month_Second_Fri29
  ,next_day(LAST_DAY(to_date('2020-02-02T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - (29 - (7 * 2)), 'fri') as FEB_Month_Third_Fri29
  ,next_day(LAST_DAY(to_date('2020-02-02T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - (29 - (7 * 3)), 'fri') as FEB_Month_Fourth_Fri29
  ,next_day(LAST_DAY(to_date('2020-02-02T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"')) - (7), 'fri') as FEB_Month_Last_Fri29
FROM DUAL;

Answer 3

Query for get last SUNDAY :

SELECT TRUNC(SYSDATE)-(select to_char(sysdate-1, 'd') FROM DUAL) FROM DUAL

Query for get last SATURDAY :

SELECT TRUNC(SYSDATE)-(select to_char(sysdate, 'd') from dual) FROM DUAL

If you want get previous SUNDAY/SATURDAY you need add to the sysdate - for example :

Previous Sunday:

SELECT TRUNC(SYSDATE)-(select to_char(sysdate, 'd')+6 FROM DUAL) FROM DUAL

Previous Saturday:

SELECT TRUNC(SYSDATE)-(select to_char(sysdate, 'd')+7 FROM DUAL) FROM DUAL

The advantage of this approach is that you can do this query each day of the week enjoy

Answer 4

Here is the quickest way to get the last Saturday or last Sunday from ANY day of the week:

Last Saturday:

select trunc(SYSDATE) - to_char(sysdate, 'd')  from dual;

Last Sunday:

select trunc(SYSDATE) - (to_char(sysdate, 'd')-1)  from dual;

Apologies for taking 6 years to give you the best answer. ;)

Answer 5

For those looking to get the last weekend days (Saturday and Sunday) on week where the first day is Monday, here's an alternative:

select today as todays_date,
       next_day(today - 7, 'sat') as prev_saturday, 
       next_day(today - 7, 'sun') as prev_sunday
from dual

Answer 6

Given your question, where the query will be run only on Mondays and the objective is to obtain the dates as stated above, one way to solve it is:

SELECT TRUNC(SYSDATE) AS TODAYS_DATE,
       TRUNC(SYSDATE)-8 AS PREVIOUS_SUNDAY,
       TRUNC(SYSDATE) - (INTERVAL '1' DAY + INTERVAL '1' SECOND) AS PREVIOUS_SATURDAY
  FROM DUAL

Share and enjoy.