SQL 计算两个日期之间相差的月份和年份

SQL 计算两个日期之间相差的月份和年份,找出两个日期之间相差多少个月或者多少年。例如,希望知道第一个和最后一个员工的入职开始日期之间相差多少个月,同时也希望把这个差值换算成年。

SQL 计算两个日期之间相差的月份和年份 问题描述

找出两个日期之间相差多少个月或者多少年。例如,希望知道第一个和最后一个员工的入职开始日期之间相差多少个月,同时也希望把这个差值换算成年。

SQL 计算两个日期之间相差的月份和年份 解决方案

一年有 12 个月,我们可以算出两个日期之间相差几个月,然后除以 12 得到相应的年份。在上述做法的基础上,可能有必要对年份的计算结果做向上或者向下的舍入。例如,EMP 表里最早的 HIREDATE17-DEC-1980,最新的则是 12-JAN-1983。如果纯粹做数学运算,二者相差三年(1983 减去 1980),然而它们的实际差值只有大约 25 个月(2 年多一点)。我们应该根据需要调整做法,本节的解决方案给出的答案将会是 25 个月和大约 2 年。
DB2 和 MySQL
使用函数 YEARMONTH 计算出给定日期的含有 4 位数字的年份和含有 2 位数字的月份。

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 位数字的年份和含有 2 位数字的月份。

 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

SQL 计算两个日期之间相差的月份和年份 扩展知识

DB2、MySQLPostgreSQL
一旦提取出 MIN_HDMAX_HD 的年份和月份,计算 MIN_HDMAX_HD 之间相差的月份和年份的方法对于所有数据库都将是相同的。接下来的讨论将涵盖这 3 种数据库。内嵌视图 X 将找出 EMP 表里最早的和最新的 HIREDATE,如下所示。

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

为了找出 MAX_HDMIN_HD 之间相差的月份,先用它们相差的年份乘以 12,然后再加上月份的差值。如果你还是不太理解这样做的原因,不妨分别打印出每个日期的年和月两个部分。下面显示了它们的值。

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

根据上述结果,MAX_HDMIN_HD 之间相差的月份就是 (1983-1980)×12 + (1-12)。为了找出两个日期之间相差的年份,就把前面计算出来的月份差值除以 12。再次强调,我们可能需要根据实际状况对年份的计算结果做出适当地舍入处理。
Oracle 和 SQL Server
内嵌视图 X 检索 EMP 表里最早的和最新的 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_BETWEENDATEDIFF)用于计算两个给定日期之间相差的月份。为了计算相差的年份,把月份差值除以 12 即可。

Python教程

Java教程

Web教程

数据库教程

图形图像教程

大数据教程

开发工具教程

计算机教程

SQL 实例