oracle日期及时间计算

1.获取当前时间与20161101的天数差

select floor(sysdate - to_date('20161101','yyyymmdd')) from dual;--8,地板值

2.获取当前日期及时间
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;--2016-11-09 10:34:05

3:获取当前时间
select to_char(sysdate,'hh24:mi:ss') from dual;--10:34:28

4.获取当前时间与20161107 000000的天数差
select ceil(sysdate - to_date('20161107 000000','yyyy-mm-dd hh24:mi:ss')) from dual;--3

5:获取两个日期之间的天数差(天花板值)
select ceil(to_date('20161109 000000','yyyy-mm-dd hh24:mi:ss') - to_date('20161107 000000','yyyy-mm-dd hh24:mi:ss')) from dual;--2


select ceil(to_date('20161109 000001','yyyy-mm-dd hh24:mi:ss') - to_date('20161107 000000','yyyy-mm-dd hh24:mi:ss')) from dual;--3


select ceil(to_date('20161109 000000','yyyy-mm-dd hh24:mi:ss') - to_date('20161106 235959','yyyy-mm-dd hh24:mi:ss')) from dual;--3

6.获取两个日期之间的月份差(天花值),月份差是按照当前日来比对,比真实情况会多算1天
select  ceil(months_between(to_date('20161108 000000','yyyy-mm-dd hh24:mi:ss'),to_date('20151107 235959','yyyy-mm-dd hh24:mi:ss'))) months from dual;--13


select  ceil(months_between(to_date('20161108 000000','yyyy-mm-dd hh24:mi:ss'),to_date('20151108 000000','yyyy-mm-dd hh24:mi:ss'))) months from dual;--12


select  ceil(months_between(to_date('20161108 000001','yyyy-mm-dd hh24:mi:ss'),to_date('20151108 000000','yyyy-mm-dd hh24:mi:ss'))) months from dual;--12


select  ceil(months_between(to_date('20161108 235959','yyyy-mm-dd hh24:mi:ss'),to_date('20151108 000000','yyyy-mm-dd hh24:mi:ss'))) months from dual;--12