SQL Query to Return All Sundays in the Last Year

This is a tweek to the previous post, which will return the dates of all the Sundays in the last year.

WITH weeks AS ( SELECT SYSDATE-((LEVEL-1)*7) weekDate, TO_NUMBER(TO_CHAR(SYSDATE,'D')) currentDay, TO_NUMBER(TO_CHAR(TO_DATE('01012012','DDMMYYYY'),'D')) targetDay FROM DUAL CONNECT BY LEVEL < 53 ) SELECT w.currentDay, w.targetDay, w.weekDate, w.weekDate-MOD((w.currentDay+7)-w.targetDay,7) previousSunday FROM weeks w

The reference to 01012012 is the key to listing Sundays, (as this date was a Sunday). To list dates for a different day just change this to whichever day you're after.

SQL Query to Return Last Particular Day of the Week

This query will return the last date of a specified day of the week. EG The date of the previous Sunday (or whichever day is set).

SELECT SYSDATE - MOD((TO_NUMBER(TO_CHAR(SYSDATE,'D'))+7) - TO_NUMBER(TO_CHAR(TO_DATE('01012012','DDMMYYYY'),'D')),7) FROM DUAL;

The date 01012012 in the above example is a Sunday. Change this to a date for whichever day of the week you're aiming for.

WITH params AS ( SELECT SYSDATE currentDate, TO_NUMBER(TO_CHAR(SYSDATE,'D')) currentDay, TO_NUMBER(TO_CHAR(TO_DATE('17082012','DDMMYYYY'),'D')) targetDay FROM DUAL ) SELECT currentDay, targetDay, currentDate-MOD((currentDay+7)-targetDay,7) FROM params;

This example produces the same result as the first, just a bit easier to read and understand.

Done 26-AUG-2012.