Oracle—常见日期型处理函数
1.sysdate—获取当前日期
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select sysdate from dual; 输出:2020-10-30 10:25:25
2.add_months()——加上月份
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select add_months(sysdate,1) from dual; 输出:2020-11-30 10:26:10 select add_months(to_date('2010-2-27','YYYY-MM-DD'),1) from dual; 输出:2010-03-27 select add_months(to_date('2010-2-28','YYYY-MM-DD'),1) from dual; 输出:2010-03-31 select add_months(to_date('2010-1-28','YYYY-MM-DD'),1) from dual; 输出:2010-02-28 select add_months(to_date('2010-1-29','YYYY-MM-DD'),1) from dual; 输出:2010-02-28 select add_months(to_date('2010-1-30','YYYY-MM-DD'),1) from dual; 输出:2010-02-28 select add_months(to_date('2010-1-30','YYYY-MM-DD'),1) from dual; 输出:2010-02-28
3.last_day()——日期所在月最后一天
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select last_day(to_date('2010-2-28','YYYY-MM-DD')) from dual; 输出:2010-02-28 select last_day(to_date('2000-2-28','YYYY-MM-DD')) from dual; 输出:2000-02-29
4.months_between()——日期差(日期天数差/31)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select months_between(to_date('2000-4-4','YYYY-MM-DD'),to_date('2000-2-6','YYYY-MM-DD')) from dual; 输出:1.93548387096774 select months_between(to_date('2000-2-6','YYYY-MM-DD'),to_date('2000-4-4','YYYY-MM-DD')) from dual; 输出:-1.93548387096774 select months_between(to_date('2000-4-4','YYYY-MM-DD'),to_date('2000-3-6','YYYY-MM-DD')) from dual; 输出:0.935483870967742
--日期天数29/31=0.935483870967742
5.next_day()——返回下周特定日期
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select next_day(to_date('2020-10-30','YYYY-MM-DD'),2) from dual;--2代表下周二 输出:2020-11-02 select next_day(to_date('2020-10-30','YYYY-MM-DD'),6) from dual;--6代表下周6 输出:2020-11-06
6.trunc()——日期截取
目的将某些信息置为0
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select trunc(sysdate,'DD') from dual; 输出:2020-10-30 select trunc(sysdate,'MM') from dual; 输出:2020-10-01 select trunc(sysdate,'MI') from dual; 输出:2020-10-30 17:25:00
7.current_date——返回当前会话当前日期
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select sessiontimezone,to_char(current_date,'yyyy-mm-dd hh:mi:ss') from dual; 输出:+08:00 2020-11-04 04:24:35 select sessiontimezone,current_timestamp from dual; 输出:+08:00 04-11月-20 04.25.00.702000 下午 +08:00
8.extract()——返回日期的某个域
语法:extract(域名,from 日期)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select extract(month from sysdate) from dual; 输出:11 select extract(hour from sysdate) from dual; 输出:报错 select extract(hour from systimestamp) from dual; 输出:8
9.to_char()——日期转换为字符串
语法:to_char(日期,格式)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select to_char(sysdate,'YYYY-MM-DD') from dual; 输出:2020-11-04
整理于《oracle入门很简单》一书