thotwielder thotwielder - 3 months ago 28
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

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.