Thursday 30 August 2012

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.

Wednesday 29 August 2012

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.