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.