|
sysdate函數(shù)：得到系統(tǒng)的當(dāng)前日期 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函數(shù)：無參數(shù),，返回系統(tǒng)時(shí)間，改時(shí)間包括時(shí)區(qū)信息,，精確到毫秒 SQL> select systimestamp from dual; SYSTIMESTAMP -------------------------------------------------------------------------------- 31-7月 -14 03.51.39.868000 下午 +08:00 dbtimezone函數(shù)：無參，返回?cái)?shù)據(jù)庫(kù)時(shí)區(qū) SQL> select dbtimezone from dual; DBTIMEZONE ---------- +00:00 add_months(date,integer)函數(shù)：該函數(shù)將返回在指定的日期上加上一個(gè)月份后的日期 date:指定的日期 integer：要加的指定月份數(shù),，如果為負(fù),，則表示減去的月份數(shù) 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函數(shù)：無參，返回當(dāng)前回話的時(shí)區(qū) SQL> select sessiontimezone from dual; SESSIONTIMEZONE --------------------------------------------------------------------------- +08:00 next_day(date,char)函數(shù):該函數(shù)返回當(dāng)前日期向后的一周char的對(duì)應(yīng)日期,，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函數(shù)：該函數(shù)得到回話時(shí)區(qū)的當(dāng)前日期 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)函數(shù)：該函數(shù)可以從指定的時(shí)間中提取到指定的日期部分,，如從給定的日期得到年、月,、分等,。 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)函數(shù)：該函數(shù)返回date1和date2 之間的月份數(shù) 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)函數(shù):該函數(shù)將返回時(shí)間date在時(shí)區(qū)timezone1轉(zhuǎn)換到時(shí)區(qū)timezone2的時(shí)間 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])函數(shù)：將date舍入到fmt指定的形式，如果參數(shù)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])函數(shù)：該函數(shù)將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)函數(shù)：該函數(shù)返回參數(shù)指定日期對(duì)應(yīng)月份的最后一天 SQL> select last_day(sysdate) from dual; LAST_DAY(SYSDATE) ----------------- 2014/7/31 16:40:2 |
|
|
