SQL 计算两个日期之间相差的月份和年份,找出两个日期之间相差多少个月或者多少年。例如,希望知道第一个和最后一个员工的入职开始日期之间相差多少个月,同时也希望把这个差值换算成年。
SQL 计算两个日期之间相差的月份和年份 问题描述
找出两个日期之间相差多少个月或者多少年。例如,希望知道第一个和最后一个员工的入职开始日期之间相差多少个月,同时也希望把这个差值换算成年。
SQL 计算两个日期之间相差的月份和年份 解决方案
一年有 12 个月,我们可以算出两个日期之间相差几个月,然后除以 12 得到相应的年份。在上述做法的基础上,可能有必要对年份的计算结果做向上或者向下的舍入。例如,EMP 表里最早的 HIREDATE
是 17-DEC-1980
,最新的则是 12-JAN-1983
。如果纯粹做数学运算,二者相差三年(1983 减去 1980),然而它们的实际差值只有大约 25 个月(2 年多一点)。我们应该根据需要调整做法,本节的解决方案给出的答案将会是 25 个月和大约 2 年。
DB2 和 MySQL
使用函数 YEAR
和 MONTH
计算出给定日期的含有 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、MySQL 和 PostgreSQL
一旦提取出 MIN_HD
和 MAX_HD
的年份和月份,计算 MIN_HD
和 MAX_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_HD
和 MIN_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_HD
和 MIN_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_BETWEEN
和 DATEDIFF
)用于计算两个给定日期之间相差的月份。为了计算相差的年份,把月份差值除以 12 即可。