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即可。

 
阅读更多
上一篇Sql确定两个日期之间的工作日数目
下一篇Sql确定两个日期之间的秒、分、小时数
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭