I need to develop a query to return the date of the last Saturday in any given month. How can I do that?
Code Listing 1: Find the last Saturday of each month for a year.
SQL> select next_day(
2 last_day(
3 add_months( trunc( sysdate,’y’),rownum-1 ) )-7,
4 to_char( to_date( ’29-01-1927′, ‘dd-mm-yyyy’ ), ‘DAY’ ) )
5 from all_objects
6 where rownum <= 12;
NEXT_DAY(
———
31-JAN-04
28-FEB-04
27-MAR-04
24-APR-04
29-MAY-04
26-JUN-04
31-JUL-04
28-AUG-04
25-SEP-04
30-OCT-04
27-NOV-04
25-DEC-04
12 rows selected.
Code Listing 2: Find the last Saturday of a given month.
SQL> select next_day(
2 last_day( to_date( ‘&YOUR_MONTH’, ‘MM’ ))-7,
3 to_char( to_date( ’29-01-1927′, ‘dd-mm-yyyy’ ), ‘DAY’ )
4 )
5 from dual;
Enter value for your_month: 01
old 2: last_day( to_date( ‘&YOUR_MONTH’, ‘MM’ ))-7,
new 2: last_day( to_date( ’01’, ‘MM’ ))-7,
NEXT_DAY(
———-
31-JAN-04
Oracle 10G Version
==================
Code Listing 3: Use SQL MODEL to find the last Saturday.
SQL> select next_day( last_day( add_months( trunc( sysdate,’y’ ),cell ) )-7,
2 to_char( to_date( ’29-jan-1927′, ‘dd-mon-yyyy’ ), ‘DAY’ ) )
3 from dual
4 model return all rows
5 dimension by (0 attr)
6 measures (0 cell)
7 rules iterate (12) (
8 cell[iteration_number] = iteration_number
9 );
Execution Plan
————————————————————
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1)
1 0 SQL MODEL (ORDERED FAST)
2 1 FAST DUAL (Cost=2 Card=1)
Statistics
————————————————————
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
666 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
12 rows processed