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

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

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

 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

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

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

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

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