Recipe8.4.Determining the Number of Months or Years Between Two Dates

Recipe 8.4. Determining the Number of Months or Years Between Two Dates

Problem

You want to find the difference between two dates in terms of either months or years. For example, you want to find the number of months between the first and last employees hired, and you also wish to express that value as some number of years.

Solution

Since there are always 12 months in a year, you can find the number of months between two dates, and then divide by 12 to get the number of years. After getting comfortable with the solution, you'll want to round the results up or down depending on what you want for the year. For example, the first HIREDATE in table EMP is "17-DEC-1980" and the last is "12-JAN-1983". If you do the math on the years (1983 minus 1980) you get three years, yet the difference in months is approximately 25 (a little over two years). You should tweak the solution as you see fit. The solutions below will return 25 months and ~2 years.

DB2 and MySQL

Use the functions YEAR and MONTH to return the four-digit year and the two-digit month for the dates supplied:

 1 select mnth, mnth/12 2   from ( 3 select (year(max_hd) - year(min_hd))*12 + 4        (month(max_hd) - month(min_hd)) as mnth 5   from ( 6 select min(hiredate) as min_hd, max(hiredate) as max_hd 7   from emp 8        ) x 9        ) y 

Oracle

Use the function MONTHS_BETWEEN to find the difference between two dates in months (to get years, simply divide by 12):

 1 select months_between(max_hd,min_hd), 2        months_between(max_hd,min_hd)/12 3   from ( 4 select min(hiredate) min_hd, max(hiredate) max_hd 5   from emp 6        ) x 

PostgreSQL

Use the function EXTRACT to return the four-digit year and two-digit month for the dates supplied:

  1 select mnth, mnth/12  2   from (  3 select ( extract(year from max_hd)  4          extract(year from min_hd) ) * 12  5        +  6        ( extract(month from max_hd)  7          extract(month from min_hd) ) as mnth  8   from (  9 select min(hiredate) as min_hd, max(hiredate) as max_hd 10   from emp 11        ) x 12        ) y 

SQL Server

Use the function DATEDIFF to find the difference between two dates in months (to get years, simply divide by 12):

 1 select datediff(month,min_hd,max_hd), 2        datediff(month,min_hd,max_hd)/12 3   from ( 4 select min(hiredate) min_hd, max(hiredate) max_hd 5   from emp 6        ) x 

Discussion

DB2, MySQL, and PostgreSQL

Once you extract the year and month for MIN_HD and MAX_HD in the PostgreSQL solution, the method for finding the months and years between MIN_HD and MAX_HD is the same for all three RDBMs. This discussion will cover all three solutions. Inline view X returns the earliest and latest HIREDATEs in table EMP and can be seen below:

  select min(hiredate) as min_hd,        max(hiredate) as max_hd   from emp MIN_HD      MAX_HD ----------- ----------- 17-DEC-1980 12-JAN-1983 

To find the months between MAX_HD and MIN_HD, multiply the difference in years between MIN_HD and MAX_HD by 12, then add the difference in months between MAX_HD and MIN_HD. If you are having trouble seeing how this works, return the date component for each date. The numeric values for the years and months are show below:

  select year(max_hd) as max_yr, year(min_hd) as min_yr,        month(max_hd) as max_mon, month(min_hd) as min_mon   from ( select min(hiredate) as min_hd, max(hiredate) as max_hd   from emp        ) x MAX_YR     MIN_YR    MAX_MON    MIN_MON ------ ---------- ---------- ----------   1983       1980          1         12 

Looking at the results above, finding the months between MAX_HD and MIN_HD is simply (19831980)*12 + (112). To find the number of years between MIN_HD and MAX_HD, divide the number of months by 12. Again, depending on the results you are looking for you will want to round the values.

Oracle and SQL Server

Inline view X returns the earliest and latest HIREDATEs in table EMP and can be seen below:

  select min(hiredate) as min_hd, max(hiredate) as max_hd   from emp MIN_HD      MAX_HD ----------- ----------- 17-DEC-1980 12-JAN-1983 

The functions supplied by Oracle and SQL Server (MONTHS_BETWEEN and DATEDIFF, respectively) will return the number of months between two given dates. To find the year, divide the number of months by 12.