LAST_DAY function
LAST_DAY(x) returns the last day of the month part of x.
The following example displays the last date in January 2012:
SQL> SELECT LAST_DAY('01-JAN-2012') FROM dual;
LAST_DAY(
---------
31-JAN-12
SQL>
Get the last day of the hiring date:
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
HIREDATE DATE);
INSERT INTO EMP VALUES (1, 'SMITH', TO_DATE('17-DEC-1980', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (2, 'ALLEN', TO_DATE('20-FEB-1981', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (3, 'WARD', TO_DATE('22-FEB-1981', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (4, 'JONES', TO_DATE('2-APR-1981', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (5, 'MARTIN',TO_DATE('28-SEP-1981', 'DD-MON-YYYY'));
SQL> select last_day(HIREDATE) from emp;
LAST_DAY(
---------
31-DEC-80
28-FEB-81
28-FEB-81
30-APR-81
30-SEP-81
SQL>