How to get the last saturday of a month?

[vnsatyasunil]

How to get the last saturday of a month?

[OraMaster]

SELECT TO_CHAR
          (NEXT_DAY (LAST_DAY (TO_DATE ('SEP', 'MM')) - 7,
                     TO_CHAR (TO_DATE ('29-01-1927', 'DD-MM-YYYY'), 'DAY')
                    ),
           'DD.MM.YYYY'
          ) "Last Saturday of SEP-2010"
  FROM DUAL;

[amitpatel66]

SQL> ed
Wrote file afiedt.buf
 
  1* select NEXT_DAY(LAST_DAY(SYSDATE) - 7,'SATURDAY') from dual
SQL> /
 
NEXT_DAY(
---------
25-SEP-10
 
SQL> 
 
-- for the complete year
 
SQL> ed
Wrote file afiedt.buf
 
  1  SELECT TO_CHAR(dat,'MON-RRRR') MOn_Year,MAX(dat) Last_Saturday
  2  FROM
  3  (select TRUNC(SYSDATE,'RRRR') + level - 1 dat,level lvl FROM
  4  dual
  5  connect by level <= TO_DATE('31-DEC-'||TO_CHAR(SYSDATE,'RRRR')) - TRUNC(SYSDATE,'RRRR') + 1)
  6  WHERE TO_CHAR(dat,'dy') = 'sat'
  7* GROUP BY TO_CHAR(dat,'MON-RRRR')
SQL> /
 
MON_YEAR       LAST_SATU
-------------- ---------
APR-2010       24-APR-10
AUG-2010       28-AUG-10
DEC-2010       25-DEC-10
FEB-2010       27-FEB-10
JAN-2010       30-JAN-10
JUL-2010       31-JUL-10
JUN-2010       26-JUN-10
MAR-2010       27-MAR-10
MAY-2010       29-MAY-10
NOV-2010       27-NOV-10
OCT-2010       30-OCT-10
SEP-2010       25-SEP-10
 
12 rows selected.
 
SQL> 
 
 

[manik161]

select next_day(last_day(sysdate)-7*level,'Saturday') from dual connect by level <=1;