sysdate函数:得到系统的当前日期
SQL> s elect to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH2
------------------------------
2014-07-31 15:49:56
systimestamp函数:无参数,返回系统时间,改时间包括时区信息,精确到毫秒
SQL> select systimestamp from dual;
SYSTIMESTAMP
--------------------------------------------------------------------------------
31-7月 -14 03.51.39.868000 下午 +08:00
dbtimezone函数:无参,返回数据库时区
SQL> select dbtimezone from dual;
DBTIMEZONE
----------
+00:00
add_months(date,integer)函数:该函数将返回在指定的日期上加上一个月份后的日期date:指定的日期
integer:要加的指定月份数,如果为负,则表示减去的月份数
SQL> select
2 to_char(add_months(to_date('2009-9-15','YYYY-MM-DD'),1),'YYYY-MM-DD'),
3 to_char(add_months(to_date('2009-9-30','YYYY-MM-DD'),1),'YYYY-MM-DD'),
4 to_char(add_months(to_date('2009-1-30','YYYY-MM-DD'),1),'YYYY-MM-DD')
5 from dual;
TO_CHAR(ADD_MONTHS(TO_DATE('20 TO_CHAR(ADD_MONTHS(TO_DATE('20 TO_CHAR(ADD_MONTHS(TO_DATE('20
------------------------------ ------------------------------ ------------------------------
2009-10-15 2009-10-31 2009-02-28
sessiontimezone函数:无参,返回当前回话的时区
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
+08:00
next_day(date,char)函数:该函数返回当前日期向后的一周char的对应日期,char表示的是星期几
SQL> select sysdate,next_day(sysdate,'星期一') from dual;
SYSDATE NEXT_DAY(SYSDATE,'星期一')
----------- --------------------------
2014/7/31 1 2014/8/4 16:02:21
SQL> select sysdate,next_day(sysdate,'星期二') from dual;
SYSDATE NEXT_DAY(SYSDATE,'星期二')
----------- --------------------------
2014/7/31 1 2014/8/5 16:06:07
SQL> select sysdate,next_day(sysdate,'星期日') from dual;
SYSDATE NEXT_DAY(SYSDATE,'星期日')
----------- --------------------------
2014/7/31 1 2014/8/3 16:06:56
current_date函数:该函数得到回话时区的当前日期
SQL> select sessiontimezone,to_char(current_date,'YYYY-MM-DD HH24:MI:SS') from dual;
SESSIONTIMEZONE TO_CHAR(CURRENT_DATE,'YYYY-MM-
--------------------------------------------------------------------------- ------------------------------
+08:00 2014-07-31 16:09:11
extract(datetime)函数:该函数可以从指定的时间中提取到指定的日期部分,如从给定的日期得到年、月、分等。
SQL> select extract(year from sysdate) year,
2 extract(minute from timestamp '2010-9-30 12:23:14') min,
3 extract(second from timestamp '2010-8-24 15:31:23') sec
4 from dual;
YEAR MIN SEC
---------- ---------- ----------
2014 23 23
months_between(date1,data2)函数:该函数返回date1和date2 之间的月份数
SQL> select months_between(to_date('2010-7-23','yyyy-mm-dd'),to_date('2010-8-23','yyyy-mm-dd')) one,
2 months_between(to_date('2010-5-31','yyyy-mm-dd'),to_date('2010-4-30','yyyy-mm-dd')) two,
3 months_between(to_date('2010-5-31','yyyy-mm-dd'),to_date('2010-9-30','yyyy-mm-dd')) three
4 from dual;
MONTHS_BETWEEN(TO_DATE('2010-7 TWO THREE
------------------------------ ---------- ----------
-1 1 -4
new_time(date,timezone1,timezone2)函数:该函数将返回时间date在时区timezone1转换到时区timezone2的时间
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:MI:SS') one,
2 to_char(new_time(sysdate,'pdt','est')) two
3 from dual;
ONE TWO
------------------- --------------
2014-07-31 16:29:13 31-7月 -14
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:MI:SS') one,
2 to_char(new_time(sysdate,'pdt','est'),'yyyy-mm-dd hh24:MI:SS') two
3 from dual;
ONE TWO
------------------- -------------------
2014-07-31 16:30:04 2014-07-31 18:30:04
round(date[,fmt])函数:将date舍入到fmt指定的形式,如果参数fmt被省略,则date被处理到最近的一条
SQL> select to_char(round(to_date('2010-7-31 16:30:23','yyyy-mm-dd hh24:MI:SS')),'yyyy-mm-dd')
2 from dual;
TO_CHAR(ROUND(TO_DATE('2010-7-
------------------------------
2010-08-01
trunc(date[,fmt])函数:该函数将date截取到fmt指定的格式,如果fmt省略,则截取到最近的日期
SQL> select to_char(trunc(to_date('2010-7-31 16:30:23','yyyy-mm-dd hh24:MI:SS')),'yyyy-mm-dd')
2 from dual;
TO_CHAR(TRUNC(TO_DATE('2010-7-
------------------------------
2010-07-31
last_day(date)函数:该函数返回参数指定日期对应月份的最后一天
SQL> select last_day(sysdate) from dual;
LAST_DAY(SYSDATE)
-----------------
2014/7/31 16:40:2