MONTHS_BETWEEN Function

The first date function in Oracle that we’ll be experimenting with is the MONTHS_BETWEEN function. As the name implies, this function is useful in determining number of months between two dates. The output of this function can be positive or negative. For the result to be positive, date1 must be later than date2. Conversely, negative result is displayed when date1 is earlier than date2.

    Syntax :

MONTHS_BETWEEN (date1,date2)

The following example statement evaluates employment tenure in months for employee number 200. The result (134.084…) is based on the difference between the current system date (in my case it is 19-NOV-2014) and the date value stored in the hire date column (i.e. 17-SEP-2003).

    SQL Statement :

SELECT    first_name,hire_date,MONTHS_BETWEEN(sysdate,hire_date) Months_Employed

FROM      employees

WHERE    employee_id=200;

    Output :