ORACLE日期时间函数

ORACLE日期时间函数 OracleÈÕÆÚº?Êý???õ2009-04-03 10:01:16| ?ÖÀà?º oracle º?Êý ÔÄ?Á2067 ÆÀÂÛ0 ×ÖºÅ?º?óÖÐÐ? ??ÔÄ . OracleÈÕÆÚº?Êý???õ(Ò?) Ò??? ??ÓÃÈÕÆÚÊý?Ý?ñÊ? 1.Y?òYY?òYYY ÄêµÄ×îºóÒ?Î???Á?Î??òÈýÎ? SQL> Select to_char(sysdate,'Y') from dual; TO_CHAR(SYSDATE,'Y') -------------------- 7 SQL> Select to_char(sysdate,'YY') from dual; TO_CHAR(SYSDATE,'YY') --------------------- 07 SQL> Select to_char(sysdate,'YYY') from dual; TO_CHAR(SYSDATE,'YYY') ---------------------- 007 2.Q ???È 1??3ÔÂΪµÚÒ????È??2?íÊ?µÚ?þ???È?? SQL> Select to_char(sysdate,'Q') from dual; TO_CHAR(SYSDATE,'Q') -------------------- 2 3.MM ÔÂ?ÝÊý SQL> Select to_char(sysdate,'MM') from dual; TO_CHAR(SYSDATE,'MM') --------------------- 05 4.RM ÔÂ?ݵÄÂÞÂí?íÊ? ??VÔÚÂÞÂíÊý×ÖÖÐ?íÊ? 5?? SQL> Select to_char(sysdate,'RM') from dual; TO_CHAR(SYSDATE,'RM') --------------------- V 5.Month ÓÃ9?ö×Ö?û???È?íÊ?µÄÔÂ?ÝÃû SQL> Select to_char(sysdate,'Month') from dual; TO_CHAR(SYSDATE,'MONTH') ------------------------ 5Ô 6.WW µ?ÄêµÚ??ÖÜ??2007Äê5ÔÂ29ÈÕΪ2007ÄêµÚ22ÖÜ?? SQL> Select to_char(sysdate,'WW') from dual; TO_CHAR(SYSDATE,'WW') --------------------- 22 7.W ??ÔµÚ??ÖÜ ??2007Äê5ÔÂ29ÈÕΪ5ÔµÚ5ÖÜ?? SQL> Select to_char(sysdate,'W') from dual; TO_CHAR(SYSDATE,'W') -------------------- 5 8.DDD µ?ÄêµÚ??Ìì??2007Äê5ÔÂ29ÈÕΪ2007ÄêµÚ149Ìì?? SQL> Select to_char(sysdate,'DDD') from dual; TO_CHAR(SYSDATE,'DDD') ---------------------- 149 9. DD µ?ÔµÚ??Ìì SQL> Select to_char(sysdate,'DD') from dual; TO_CHAR(SYSDATE,'DD') --------------------- 29 10.D ÖÜÄÚµÚ??Ìì SQL> Select to_char(sysdate,'D') from dual; TO_CHAR(SYSDATE,'D') -------------------- 3 11.DY ÖÐÎĵÄÐÇÆÚ?? ????2007Äê5ÔÂ29ÈÕΪÐÇÆÚ?þ???? SQL> Select to_char(sysdate,'DY') from dual; TO_CHAR(SYSDATE,'DY') --------------------- ÐÇÆÚ?þ 12.HH?òHH12 12?øÖÆÐ?Ê?Êý??16?º09?ÖΪÓÃ12Ð?Ê?ÖÆ?ÆÊ?Ϊ4µã?? SQL> Select to_char(sysdate,'HH') from dual; TO_CHAR(SYSDATE,'HH') --------------------- 04 13.HH24 24Ð?Ê?ÖÆ SQL> Select to_char(sysdate,'HH24') from dual; TO_CHAR(SYSDATE,'HH24') ----------------------- 16 OracleÈÕÆÚº?Êý???õ(?þ) ?þ????ÓÃÊ??äº?Êý 1.trunc(sysdate,'Q') ?????ȵÚÒ?Ìì SQL> select trunc(sysdate,'Q') from dual; TRUNC(SYSDATE,'Q') ------------------ 2007-4-1 2.trunc(sysdate,'D') ??ÖܵĵÚÒ?Ìì(ÖÜÈÕ) SQL> select trunc(sysdate,'D')from dual; TRUNC(SYSDATE,'D') ------------------ 2007-5-27 3.last_day(sysdate) ??ÔÂ×îºóÒ?Ìì SQL> select last_day(sysdate) from dual; LAST_DAY(SYSDATE) ----------------- 2007-5-31 15:20:3 4.add_months(sysdate,2) ÈÕÆÚsysdateºóÍÆ2?öÔ SQL> select add_months(sysdate,2) from dual; ADD_MONTHS(SYSDATE,2) --------------------- 2007-7-29 15:21:14 5.next_day(sysdate,2) ÈÕÆÚsysdateÖ?ºóµÄµÚÒ?ÖÜÖÐ,µÚ2(Ö???ÐÇÆڵĵÚ??Ìì)ÊÇÊ?Ã?ÈÕÆÚ SQL> select next_day(sysdate,2) from dual; NEXT_DAY(SYSDATE,2) ------------------- 2007-6-4 15:22:10 6.Months_between(f,s) ÈÕÆÚfºÍs?äÏà?îÔÂÊý SQL> select months_between(sysdate,to_date('2007-04-12','yyyy-mm-dd'))from dual; MONTHS_BETWEEN(SYSDATE,TO_DATE ------------------------------ 1.56909908900836 7.µÃµ?SYSDATE+5ËùÔÚµÄÔÂ?Ý SQL> SELECT to_char(SYSDATE+5,'mon','nls_date_language=american') FROM dual; TO_CHAR(SYSDATE+5,'MON','NLS_D ------------------------------ jun 8.current_date()?µ?ص?Ç??á??Ê?ÇøÖеĵ?Ç?ÈÕÆÚ?? 9.select dbtimezone from dual; 10.extract()ÕÒ?öÈÕÆÚ?ò?ä?ôÖµµÄ×Ö?ÎÖµ SQL> select extract(month from sysdate) "This Month" from dual; This Month ---------- 5 SQL> select extract(year from sysdate) "This year" from dual; This year ---------- 2007 SQL> select extract(month from add_months(sysdate,2)) " Month" from dual; Month ---------- 7 ================================================================== OracleÈÕÆÚº?Êý???õ(Èý) Èý??Ò?Ð?ʵ?ùºóµÄÓÃ???º 1.ÉÏÔÂÄ?Ìì?º select to_char(add_months(last_day(sysdate),-1),'yyyy-MM-dd') LastDay from dual?? 2.ÉÏÔÂ?ñÌì SQL> select to_char(add_months(sysdate,-1),'yyyy-MM-dd') PreToday from dual; 3.ÉÏÔÂÊ×Ìì SQL> select to_char(add_months(last_day(sysdate)+1,-2),'yyyy-MM-dd') firstDay from dual; 4.ÒªÕÒµ?Ä?ÔÂÖÐËùÓÐÖÜÎåµÄ?ßÌåÈÕÆÚ SELECT to_char(b.a,'YY-MM-DD') FROM ( SELECT trunc(SYSDATE,'mm')+ROWNUM-1 a FROM dba_objects where rownum < 32 ) b WHERE to_char(b.a,'day')='ÐÇÆÚÎå'; Èç?û?Ñwhere to_char(t.d, 'MM') = to_char(sysdate, 'MM')?Ä?Ésysdate-90????Ϊ?éÕÒµ?Ç?ÔÂ?ݵÄÇ?Èý?öÔÂÖÐ µÄÃ?ÖÜÎåµÄÈÕÆÚ?? 5.µÃµ?ϵÍ?µ?Ç?ÔÂ??ÒÔºóµÄÈÕÆÚ select trunc(sysdate, 'MM')+ROWNUM-1 FROM dba_objects ; ----------------------------------- to_date ×Ö?û??ÀàÐÍתΪ??ÈÕÆÚÀàÐÍ ×Ö?û??ÖеÄÏàÓ?Î?ÖÃÉϵÄ×Ö?û,?ØÐë?ûºÏÊ??ä??Î?µÄÏÞÖÆ 14.MI ?ÖÖÓÊý(0??59) ÌáÊ?×?Òâ??Òª??MM?ñÊ?ÓÃÓÚ?ÖÖÓ(?ÖÖÓÓ??ÃÊ?ÓÃMI)??MMÊÇÓÃÓÚÔÂ?ݵÄ?ñÊ?????ËüÓÃÓÚ?ÖÖÓÒ?ÄÜ??×???µ??á?ûÊÇ?íÎóµÄ?? 15.SS ÃëÊý(0??59) ×Ü?á?ºoracleÊ??ä?Ù×?ÓÃ?Ó?õ???Í?ÉÒÔ???ù??µ?Î?Ϊ1Ìì??20Ð?Ê?ÒÔºóµÄÊ??äÓ??ÃÔõÑù?ÆËã? 20Ð?Ê?ÒªÕÛËãµ??ÖÖÓ,?ÍÊÇ1/1440,1Ð?Ê??ÍÊÇ1/24,20Ð?Ê?Ò??ÉÒÔÓÃ20/24?òÕß1200/1400,120/144À??íÊ???select to_date( '2007-9-20 9:52:15 ', 'yyyy-mm-dd hh24:mi:ss ')+20/24 from dual;Õâ?ö?ÍÊÇ?Ó20Ð?Ê?ÁË