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