The initial question asks how to select the last SATURDAY of each month, not the last day.

the initial question actually was hiding the question in the subject and I read the questions, not the "subject".  So no, I did not see that.


but easy enough:

ops$tkyte@ORA9IR2> select next_day(
  2           last_day(
  3               add_months(trunc(sysdate,'y'),rownum-1) )-7,
  4               to_char(to_date('29-jan-1927', 'dd-mon-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.
 
It is even international now. 

select dat, day from
(select last_day(&&dat)-(rownum-1) dat, to_char(last_day(&&dat)-(rownum-1), 'DY') day
from all_objects
where rownum <= 7)
where day = 'SAT'

My previous rating of "Not useful" was wrong after all. That "from all_objects" trick is quite interesting.

and I intrepreted the question as "get me all of the last saturdays of each month" meaning "12 months in the year"

to get just the last saturday for a given month would be simply the next_day of the last_day minus 7:

ops$tkyte@ORA9IR2> edit
Wrote file afiedt.buf
 
  1  select next_day(
  2              last_day( to_date( '&YOUR_MONTH', 'MON' ))-7,
  3                 to_char(to_date('29-jan-1927', 'dd-mon-yyyy'), 'DAY' )
  4       )
  5*   from dual
ops$tkyte@ORA9IR2> /
Enter value for your_month: JAN
old   2:             last_day( to_date( '&YOUR_MONTH', 'MON' ))-7,
new   2:             last_day( to_date( 'JAN', 'MON' ))-7,
 
NEXT_DAY(
---------
31-JAN-04
 
ops$tkyte@ORA9IR2> /
Enter value for your_month: MAR
old   2:             last_day( to_date( '&YOUR_MONTH', 'MON' ))-7,
new   2:             last_day( to_date( 'MAR', 'MON' ))-7,
 
NEXT_DAY(
---------
27-MAR-04
 

set autotrace on

select next_day(last_day(add_months(trunc(sysdate,'y'),cell) )-7, to_char(to_date('29-jan-1927', 'dd-mon-yyyy'), 'DAY' ) )
from dual
model return all rows
dimension by (0 attr)
measures (0 cell)
rules iterate (12) (
cell[iteration_number] = iteration_number
);

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.


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

10g sql - very cool.

Very cool.

What was the point of:

to_char(to_date('29-jan-1927', 'dd-mon-yyyy'), 'DAY' )

Can't you just plug in 'SATURDAY' ?

ops$tkyte@ORA9IR2> select to_char(sysdate-1,'day') from dual;
 
TO_CHAR(S
---------
saturday
 
ops$tkyte@ORA9IR2> alter session set nls_language='French';
 
Session altered.
 
ops$tkyte@ORA9IR2> select to_char(sysdate-1,'day') from dual;
 
TO_CHAR(
--------
samedi
 


I wanted it to be "international".  Everytime I do something that isn't -- someone points that out.

I picked 1972 for no apparent reason.  it was just a year that I new a saturday in. any day that is saturday would work. 

To the Last Comment ,

"Can't you just plug in 'SATURDAY' ? "
Hmmm, and what will happen if your NLS_ Session Settings are in Germany ?

Why not just plug in 'SAMSTAG' :)

so , you get the point

Tom:

I saw your coverage of this topic in the November/December 2004 Oracle Magazine, and your credit to Ant for the "getless" version using the 10G modelling clause, very nice.  

Coincidentally, you also had an article on pipelined functions in the same set of questions. I wanted to play with this feature, so I put the two together. You can do a "getless" version in 9.2 as well.

SQL> CREATE TYPE sat_scalar_type AS OBJECT (last_sat DATE);
  2  /

Type created.

SQL> CREATE TYPE sat_type AS TABLE OF sat_scalar_type;
  2  /

Type created.

SQL> CREATE OR REPLACE FUNCTION last_sat (p_mon_year  IN VARCHAR2,
  2                                       p_no_months IN NUMBER)
  3  RETURN sat_type PIPELINED AS
  4     l_sdt DATE;
  5     l_sat DATE;
  6  BEGIN
  7     l_sdt := LAST_DAY(TO_DATE(p_mon_year,'MON-YYYY'));
  8     FOR i IN 0 .. p_no_months - 1 LOOP
  9        l_sat := NEXT_DAY(ADD_MONTHS(l_sdt, i)-7,TO_CHAR(TO_DATE( '29-jan-1927', 'dd-mon-yyyy' ), 'DAY' ));
 10        PIPE ROW(sat_scalar_type(l_sat));
 11     END LOOP;
 12          RETURN;
 13  END;
 14  /

Function created.

SQL> SELECT last_sat FROM TABLE(last_sat('JAN-2004',12));

LAST_SAT
-----------
31-JAN-2004
28-FEB-2004
27-MAR-2004
24-APR-2004
29-MAY-2004
26-JUN-2004
31-JUL-2004
28-AUG-2004
25-SEP-2004
30-OCT-2004
27-NOV-2004
25-DEC-2004

12 rows selected.

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        686  bytes sent via SQL*Net to client
        651  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         12  rows processed

No need to wait for 10g. 

...but only due to parsing...

SQL> set autotrace traceonly
SQL>
SQL> get afiedt.buf
  1  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*       )
SQL> /

12 rows selected.


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
----------------------------------------------------------
       2146  recursive calls
        192  db block gets
        577  consistent gets
         60  physical reads
          0  redo size
        669  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         57  sorts (memory)
          0  sorts (disk)
         12  rows processed

SQL> /

12 rows selected.


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
        669  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

Still very cool !
 

Dear Tom,
How to find out the 2nd "Saturday" of each month fortnightly??

I need to find the date if the week of the month and day is given as an input along with month and year.
say if 3rd week wednesday,aug 2006 as inout then i should get righ date.

It is August 21st as 3rd Monday of August,2006 falls on 21st.

ops$tkyte%ORA10GR2> variable week number
ops$tkyte%ORA10GR2> variable day  varchar2(3)
ops$tkyte%ORA10GR2> variable month varchar2(3)
ops$tkyte%ORA10GR2> variable year number
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec :week := 3; :day := 'Mon'; :month := 'Aug'; :year := 2006;

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select next_day(to_date( :month || '-' || to_char(:year,'fm0000'), 'mon-yyyy' )-1,:day)
  2         + (:week-1) * 7
  3    from dual;

NEXT_DAY(
---------
21-AUG-06