SQL determines the number of months or years between two dates

Problem

Calculate the number of months or years of difference between two dates. For example, calculate the number of months for the first employee and the last employee, and the number of years for these months.

Solution

Because there are 12 months in a year, the number of years can be obtained after the number of months between two dates is obtained and then divided by 12. After a corresponding solution is available, the results can be included or included based on the different purposes of the number of years. For example, the first hiredate (employment date) in the table EMP is "17-dec-1980", and the last hiredate is "12-Jan-1983 ". If the subtraction operation is performed on the Year (1983 minus 1980), the result is 3 years. However, the month difference is about 25 (more than two years ). Therefore, you should modify the solution. The results returned by the following solutions are 25 months and 2 years.

DB2 and MySQL

Use the year and month functions to return a four-digit year and two-digit month for a given date:

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

Using the months_between function, you can get the number of months of difference between two dates (to get the number of years of difference, you only need to divide it 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 extract function to return a four-digit year and two-digit month for a given date:

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 datediff function to obtain the number of months of difference between two dates (to obtain the number of years of difference, 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

Except that the year and month are extracted from min_hd and max_hd In the PostgreSQL solution, for the three rdbm, the year of difference and the number of months between min_hd and max_hd are calculated in the same way. The following describes solutions applicable to these three databases. Inline view X returns the first hiredate and the last hiredate In the EMP table, as shown 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 calculate the number of months between min_hd and max_hd, multiply the number of years difference by 12, and then add the difference between min_hd and max_hd. If you do not know the mechanism, you can display the relevant parts of the two dates. Their values for the year and month are as follows:

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

Observe the above results and you will find that the difference between min_hd and max_hd is (1983-1980) * 12 + (1-12 ). To obtain the number of years of difference between min_hd and max_hd, you only need to divide it by 12. Of course, you also need to perform the corresponding house/in operation on the number of years of difference according to the purpose.

Oracle and SQL Server

Inline view X returns the first hiredate and the last hiredate In the EMP table, as shown below:

Select min (hiredate) as min_hd, max (hiredate) as max_hd

From EMP

Min_hd max_hd

----------------------

17-dec-1980 12-Jan-1983

Functions provided by Oracle and SQL Server (months_between and datediff respectively) can return the number of months between two given dates. To get the number of years, you only need to divide it by 12.