oracle常用日期函数

1、sysdate
获取系统当前日期和时间
SQL> select sysdate from dual;

SYSDATE
-----------
2008-12-12

2、to_date
将字符串转化为日期类型
SQL> select to_date('2008-10-10 15:23:24','yyyy-mm-dd hh24:mi:ss') from dual;

TO_DATE('2008-10-1015:23:24','
------------------------------
2008-10-10 下午 03:23:24

3、trunc(date[,fromat])
按指定格式截断日期，format为可用的日期格式化字符串

SQL> select trunc(sysdate,'yy') from dual;

TRUNC(SYSDATE,'YY')
-------------------
2008-1-1

SQL> select trunc(sysdate,'mm') from dual;

TRUNC(SYSDATE,'MM')
-------------------
2008-12-1

SQL> select trunc(sysdate,'dd') from dual;

TRUNC(SYSDATE,'DD')
-------------------
2008-12-12

4、round(date[,formate])
按指定格式对日期进行四舍五入操作
SQL> select round(sysdate,'dd') from dual;

ROUND(SYSDATE,'DD')
-------------------
2008-12-13

SQL> select round(sysdate,'mm') from dual;

ROUND(SYSDATE,'MM')
-------------------
2008-12-1

SQL> select round(to_date('2008-12-16','yyyy-mm-dd'),'mm') from dual;

ROUND(TO_DATE('2008-12-16','YY
------------------------------
2009-1-1

SQL> select round(sysdate,'yyyy') from dual;

ROUND(SYSDATE,'YYYY')
---------------------
2009-1-1

5、Last_day
本月最后一天
SQL> select last_day(sysdate) from dual;

LAST_DAY(SYSDATE)
-----------------
2008-12-31 下午 0

6、Add_months(d,n)
当前日期d后推n个月，用于从一个日期值增加或减少一些月份
date_value:=add_months(date_value,number_of_months)

SQL> select add_months(to_date('2008-10-31','yyyy-mm-dd'),1) from dual;
SQL> select add_months(to_date('2008-10-30','yyyy-mm-dd'),1) from dual;

ADD_MONTHS(TO_DATE('2008-10-30
------------------------------
2008-11-30

SQL> select add_months(to_date('2008-10-29','yyyy-mm-dd'),1) from dual;

ADD_MONTHS(TO_DATE('2008-10-29
------------------------------
2008-11-29

7、Months_between(f,s)
日期f和s间相差月数
SQL> select months_between(to_date('2008-12-01','yyyy-mm-dd'),to_date('2008-11-01','yyyy-mm-dd')) from dual;

MONTHS_BETWEEN(TO_DATE('2008-1
------------------------------
1

SQL> select months_between(to_date('2008-12-01','yyyy-mm-dd'),to_date('2008-11-30','yyyy-mm-dd')) from dual;

MONTHS_BETWEEN(TO_DATE('2008-1
------------------------------
0.0645161290322581