MONTHS_BETWEEN () Function
Using this method in PL/SQL you can calculate the number of months between two entered dates date1 and date2. if date1 is later than date2 then the result would be positive and if date1 is earlier than date2 then result is negative.
Note: If a fractional month is calculated, the MONTHS_BETWEEN function calculates the fraction based on a 31-day month.
MONTHS_BETWEEN () Function Example
Query :
SELECT MONTHS_BETWEEN (TO_DATE ('01-07-2003', 'dd-mm-yyyy'),
TO_DATE ('14-03-2003', 'dd-mm-yyyy')) AS NUMBER_OF_MONTHS
FROM Dual
SELECT MONTHS_BETWEEN (TO_DATE ('01-07-2003', 'dd-mm-yyyy'),
TO_DATE ('14-03-2003', 'dd-mm-yyyy')) AS NUMBER_OF_MONTHS
FROM Dual
Output:
| NUMBER_OF_MONTHS |
|---|
| 3.58 |
Explanation: Here date1 and date2 are not on the same day of the month that’s why we are getting the value in fractions, as well as date1 is later than date2 so the resulting value is in integers. Entered date should be in particular date format, that is the reason of using TO_DATE function while comparison within MONTHS_BETWEEN function. Let’s select the number of months an employee has worked for the company.
Date and Time Functions in PL/SQL
Date and Time Functions in PL/SQL are useful for date and time operations like extracting date components, performing date arithmetic, and converting between date formats.
Contact Us