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

DB2和MySQL

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

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

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

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

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

Oracle和SQL Server

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

from emp

MIN_HD      MAX_HD

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

17-DEC-1980 12-JAN-1983

• 广告
• 抄袭
• 版权
• 政治
• 色情
• 无意义
• 其他

120