Sql确定两个日期之间的月份数或年数

问题

求两个日期之间相差的月数或年数。例如，求第一个员工和最后一个员工聘用之间相差的月份数，以及这些月折合的年数。

解决方案

由于一年有12个月，因此，获得两个日期之间的月份数之后，再除以12，就能得到年数。在有了相应的解决方案 后，可以根据此年数的不同用途对结果进行舍/入。例如，表EMP中的第一个HIREDATE（聘用日期）是“17-DEC-1980”，最后一个 HIREDATE是“12-JAN-1983”。如果对年进行减法运算（1983减去1980），结果是3年。然而，月份差大约为25（两年多一点儿）。 所以应该修改解决方案。下列的解决方案返回的结果是25个月及2年。

DB2和MySQL

使用函数YEAR和MONTH为给定日期返回4位数的年份和两位数的月份：

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

使用函数MONTHS_BETWEEN，将得到两个日期之间相差的月数（要得到相差年数，只需除以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

使用函数EXTRACT，为给定日期返回4位数的年和两位数的月：

 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

使用函数DATEDIFF，得到两个日期之间相差的月数（要得到相差年数，只需除以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

讨论

DB2、MySQL和PostgreSQL

除PostgreSQL解决方案中从MIN_HD和MAX_HD提取了年份、月份的方法不同外，对于这3个 RDBM，计算MIN_HD和MAX_HD之间相差年数和月数的方法都相同。下面的讨论适用于这3种数据库的解决方案。内联视图X返回表EMP中第一个 HIREDATE和最后一个HIREDATE，如下所示：

select min(hiredate) as min_hd,

       max(hiredate) as max_hd

  from emp

MIN_HD      MAX_HD

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

17-DEC-1980 12-JAN-1983

要计算MIN_HD和MAX_HD 之间的月数，只需用年数差乘以12，然后再加上MIN_HD和MAX_HD之间的月数之差。如果不知道其中的机理，可以将这两个日期的有关部分显示出来。它们对年和月部分的数值如下所示：

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

观察上面的结果，会发现MIN_HD和MAX_HD之间相差的月数是(1983-1980)*12 + （1-12）。要得到MIN_HD和MAX_HD之间相差的年数，只需除以12即可，当然，还要根据用途，对相差年数进行相应的舍/入操作。

Oracle和SQL Server

内联视图X返回表EMP中第一个HIREDATE和最后一个HIREDATE，如下所示：

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

  from emp

MIN_HD      MAX_HD

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

17-DEC-1980 12-JAN-1983

由Oracle和SQL Server提供的函数（分别为MONTHS_BETWEEN和DATEDIFF）可以返回两个给定日期之间的月份数。要得到年数，只需除以12即可。