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]

[code=oracle]
SQL> ed
Wrote file afiedt.buf

1* select NEXT_DAY(LAST_D AY(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,'MO N-RRRR') MOn_Year,MAX(da t) 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(SYSD ATE,'RRRR')) - TRUNC(SYSDATE,' RRRR') + 1)
6 WHERE TO_CHAR(dat,'dy ') = 'sat'
7* GROUP BY TO_CHAR(dat,'MO N-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>

[/code]

[manik161]

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