thotwielder thotwielder - 1 year ago 83
SQL Question

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

Answer Source

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.