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.