Q:

oracle sql get last Sunday and last Saturday

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

A:

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

A:

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

A:

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.

