Oracle PL/SQL - MONTHS_BETWEEN function

MONTHS_BETWEEN Function : 

Syntax : MONTHS_BETWEEN ('Date1','Date2');

• In Oracle PL/SQL MONTHS_BETWEEN function returns the number of months between two dates.
• Input argument to the MONTHS_BETWEEN function should be of data type 'date'.
• Return Type of MONTHS_BETWEEN is a number.
• If Date 1 is greater than Date 2 then the return number is positive
• If Date 1 is less than Date 2 then the return number is negative
• If Date 1 and Date 2  have the same day component or are the last day of the month, the return number is a whole number,Otherwise Oracle Database calculates the fractional portion of the result based on a 31-day month and considers the difference in time components Date1 and Date2.

EXAMPLE 1 : ( Date 1 is greater than Date 2)

SELECT MONTHS_BETWEEN (TO_DATE('02-02-2015','MM-DD-YYYY'),
       TO_DATE('01-01-2015','MM-DD-YYYY') ) "Months"
    FROM DUAL;

    Months
----------
1.03225806

EXAMPLE 2 : ( Date 1 is less than Date 2)

SELECT MONTHS_BETWEEN (SYSDATE,SYSDATE+1) "Months"
    FROM DUAL;

Months
----------
-.03225806

EXAMPLE 3 : ( Date 1 and Date 2 has same day)

SELECT MONTHS_BETWEEN (TO_DATE ('2015-10-25', 'YYYY-MM-DD'), TO_DATE ('2015-03-25', 'YYYY-MM-DD')) "Months"
       FROM DUAL;

Months
----------
7

Oracle PL/SQL - LAST_DAY function

LAST_DAY Function : 

Syntax : LAST_DAY ('Date');

• In Oracle PL/SQL LAST_DAY function returns the last day of the input date argument.
• Input argument to the LAST_DAY function should be of data type 'date'.
• Return Type of LAST_DAY function is always date.

In the below Example 1 the LAST_DAY returns the last day for the month of March 2015.

In Example 2 , since it is a leap year the last day of the February month is 29th.

EXAMPLE :1 

SELECT SYSDATE,
   LAST_DAY(SYSDATE) "Month_End",
     FROM DUAL;
 
SYSDATE   MONTH_END 
--------- ---------
25-MAR-15 31-MAR-15 

EXAMPLE 2 :

SELECT LAST_DAY('20-FEB-2016') "MONTH_END"
FROM DUAL;

MONTH_END
---------------
29-FEB-16