ORACLE日期函数.docx

ORACLE日期函数.docx

《ORACLE日期函数.docx》由会员分享，可在线阅读，更多相关《ORACLE日期函数.docx（16页珍藏版）》请在冰豆网上搜索。

ORACLE日期函数

oracleÈÕÆÚº¯Êý

trunc（sysdate,'Q'）--±¾¼¾¶ÈµÚÒ»Ìì

trunc（sysdate,'D'）--±¾ÖܵĵÚÒ»Ìì（ÖÜÈÕ）

²éѯOracleÈÕÆÚ¸ñʽ

----------------------------------

select*fromnls_database_parameters;

µÃµ½½á¹ûÈçϱí:

±íÖÐNLS_DATE_FORMAT±íʾÈÕÆÚ¸ñʽ.

PARAMETERVALUE

----------------------------------------------------------------------

NLS_LANGUAGEAMERICAN

NLS_TERRITORYAMERICA

NLS_CURRENCY$

NLS_ISO_CURRENCYAMERICA

NLS_NUMERIC_CHARACTERS.,

NLS_CHARACTERSETZHS16GBK

NLS_CALENDARGREGORIAN

NLS_DATE_FORMATDD-MON-RR

NLS_DATE_LANGUAGEAMERICAN

NLS_SORTBINARY

NLS_TIME_FORMATHH.MI.SSXFFAM

NLS_TIMESTAMP_FORMATDD-MON-RRHH.MI.SSXFFAM

NLS_TIME_TZ_FORMATHH.MI.SSXFFAMTZH:

TZM

NLS_TIMESTAMP_TZ_FORMATDD-MON-RRHH.MI.SSXFFAMTZH:

TZM

NLS_DUAL_CURRENCY$

NLS_COMPBINARY

NLS_NCHAR_CHARACTERSETZHS16GBK

NLS_RDBMS_VERSION8.1.7.0.0

»òÕß²éѯV$NLS_PARAMETERS±í,

select*fromV$NLS_PARAMETERS;

Ò²ÓÐÀàËƽá¹û.

to_date×Ö·û´®ÀàÐÍתΪ»»ÈÕÆÚÀàÐÍ

×Ö·û´®ÖеÄÏàӦλÖÃÉϵÄ×Ö·û,±ØÐë·ûºÏʱ¼ä·¶Î§µÄÏÞÖÆ

SQL>selectto_date（'2004-11-1212-07-32','yyyy-mm-ddhh24-mi-ss'）valuefromdual;

VALUE

-------------------

2004.11.1212:

07:

32

SQL>selectto_date（'20041015'）valuefromdual;

VALUE

-------------------

2004.10.1500:

00:

00

SQL>selectto_date（'20041315'）valuefromdual;

ERRORλÓÚµÚ1ÐÐ:

ORA-01861:

ÎÄ×ÖÓë¸ñʽ×Ö·û´®²»Æ¥Åä

sysdateµ±Ç°ÈÕÆÚºÍʱ¼ä

SQL>selectsysdatevaluefromdual;

VALUE

-------------------

2003.11.2317:

09:

01

last_day±¾ÔÂ×îºóÒ»Ìì

SQL>selectlast_day（sysdate）valuefromdual;

VALUE

-------------------

2003.11.3017:

08:

17

add_months（d,n）ÈÕÆÚdºóÍÆn¸öÔÂ

SQL>selectadd_months（sysdate,2）valuefromdual;

VALUE

-------------------

2005.01.2317:

10:

21

next_day（d,day）ÈÕÆÚdÖ®ºóµÄµÚÒ»ÖÜÖÐ,Ö¸¶¨µÄÄÇÌì（Ö¸¶¨ÐÇÆڵĵڼ¸Ìì）ÊÇʲôÈÕÆÚ

SQL>selectnext_day（sysdate,1）valuefromdual;

VALUE

-------------------

2004.11.2817:

38:

55

ÏàÐźܶàÈ˶¼Óйýͳ¼ÆijЩÊý¾ÝµÄ¾Àú£¬±ÈÈ磬Ҫͳ¼Æ²ÆÎñµÄÇé¿ö£¬¿ÉÄÜÒª°´Ã¿Ä꣬ÿ¼¾¶È£¬Ã¿Ô£¬ÉõÖÁÿ¸öÐÇÆÚÀ´·Ö±ðͳ¼Æ¡£ÄÇÔÚoracleÖÐÓ¦¸ÃÔõôÀ´Ð´sqlÓï¾äÄØ£¬Õâ¸öʱºòOracleµÄÈÕÆÚº¯Êý»á¸øÎÒÃǺܶà°ïÖú¡£

³£ÓÃÈÕÆÚÐͺ¯Êý

1¡£Sysdateµ±Ç°ÈÕÆÚºÍʱ¼ä

SQL>Selectsysdatefromdual;

SYSDATE

----------

21-6ÔÂ-05

2¡£Last_day±¾ÔÂ×îºóÒ»Ìì

SQL>Selectlast_day（sysdate）fromdual;

LAST_DAY（S

----------

30-6ÔÂ-05

3¡£Add_months（d,n）µ±Ç°ÈÕÆÚdºóÍÆn¸öÔÂ

ÓÃÓÚ´ÓÒ»¸öÈÕÆÚÖµÔö¼Ó»ò¼õÉÙһЩÔ·Ý

date_value:

=add_months（date_value,number_of_months）

SQL>Selectadd_months（sysdate,2）fromdual;

ADD_MONTHS

----------

21-8ÔÂ-05

4¡£Months_between（f,s）ÈÕÆÚfºÍs¼äÏà²îÔÂÊý

SQL>selectmonths_between（sysdate,to_date（'2005-11-12','yyyy-mm-dd'））fromdual;

MONTHS_BETWEEN（SYSDATE,TO_DATE（'2005-11-12','YYYY-MM-DD'））

----------------------------------------------------------

-4.6966741

5¡£NEXT_DAY（d,day_of_week）

·µ»ØÓÉ"day_of_week"ÃüÃûµÄ£¬ÔÚ±äÁ¿"d"Ö¸¶¨µÄÈÕÆÚÖ®ºóµÄµÚÒ»¸ö¹¤×÷ÈÕµÄÈÕÆÚ¡£²ÎÊý"day_of_week"±ØÐëΪ¸ÃÐÇÆÚÖеÄijһÌì¡£

SQL>SELECTnext_day（to_date（'20050620','YYYYMMDD'）,1）FROMdual;

NEXT_DAY（T

----------

26-6ÔÂ-05

6¡£current_date（）·µ»Øµ±Ç°»á»°Ê±ÇøÖеĵ±Ç°ÈÕÆÚ

date_value:

=current_date

SQL>columnsessiontimezonefora15

SQL>selectsessiontimezone,current_datefromdual;

SESSIONTIMEZONECURRENT_DA

-------------------------

+08:

0013-11ÔÂ-03

SQL>altersessionsettime_zone='-11:

00'2/

»á»°ÒѸü¸Ä¡£

SQL>selectsessiontimezone,current_timestampfromdual;

SESSIONTIMEZONECURRENT_TIMESTAMP

---------------------------------------------------

-11:

0012-11ÔÂ-0304.59.13.668000ÏÂÎç-11:

00

7¡£current_timestamp（）ÒÔtimestampwithtimezoneÊý¾ÝÀàÐÍ·µ»Øµ±Ç°»á»°Ê±ÇøÖеĵ±Ç°ÈÕÆÚ

SQL>selectcurrent_timestampfromdual;

CURRENT_TIMESTAMP

---------------------------------------------------------------------------

21-6ÔÂ-0510.13.08.220589ÉÏÎç+08:

00

8¡£dbtimezone（）·µ»ØʱÇø

SQL>selectdbtimezonefromdual;

DBTIME

------

-08:

00

9¡£extract（）ÕÒ³öÈÕÆÚ»ò¼ä¸ôÖµµÄ×Ö¶ÎÖµ

date_value:

=extract（date_fieldfrom[datetime_value|interval_value]）

SQL>selectextract（monthfromsysdate）"ThisMonth"fromdual;

ThisMonth

----------

6

SQL>selectextract（yearfromadd_months（sysdate,36））"Years"fromdual;

Years

----------

2008

10¡£localtimestamp（）·µ»Ø»á»°ÖеÄÈÕÆÚºÍʱ¼ä

SQL>selectlocaltimestampfromdual;

LOCALTIMESTAMP

---------------------------------------------------------------------------

21-6ÔÂ-0510.18.15.855652ÉÏÎç

³£ÓÃÈÕÆÚÊý¾Ý¸ñʽ£¨¸Ã¶ÎΪժ³£©

Y»òYY»òYYYÄêµÄ×îºóһ룬Á½Î»»òÈýλSelectto_char（sysdate,¡¯YYY¡¯）fromdual£»002±íʾ2002Äê

SYEAR»òYEARSYEARʹ¹«ÔªÇ°µÄÄê·ÝÇ°¼ÓÒ»¸ººÅSelectto_char（sysdate,¡¯SYEAR¡¯）fromdual£»-1112±íʾ¹«ÔªÇ°1112Äê

Q¼¾¶È£¬1¡«3ÔÂΪµÚÒ»¼¾¶ÈSelectto_char（sysdate,¡¯Q¡¯）fromdual£»2±íʾµÚ¶þ¼¾¶È¢Ù

MMÔ·ÝÊýSelectto_char（sysdate,¡¯MM¡¯）fromdual£»12±íʾ12ÔÂ

RMÔ·ݵÄÂÞÂí±íʾSelectto_char（sysdate,¡¯RM¡¯）fromdual£»IV±íʾ4ÔÂ

MonthÓÃ9¸ö×Ö·û³¤¶È±íʾµÄÔ·ÝÃûSelectto_char（sysdate,¡¯Month¡¯）fromdual£»Mayºó¸ú6¸ö¿Õ¸ñ±íʾ5ÔÂ

WWµ±ÄêµÚ¼¸ÖÜSelectto_char（sysdate,¡¯WW¡¯）fromdual£»24±íʾ2002Äê6ÔÂ13ÈÕΪµÚ24ÖÜ

W±¾Ôµڼ¸ÖÜSelectto_char（sysdate,¡¯W¡¯）fromdual£»2002Äê10ÔÂ1ÈÕΪµÚ1ÖÜ

DDDµ±ÄêµÚ¼¸,1ÔÂ1ÈÕΪ001£¬2ÔÂ1ÈÕΪ032Selectto_char（sysdate,¡¯DDD¡¯）fromdual£»3632002Äê12ÔÂ29ÈÕΪµÚ363Ìì

DDµ±Ôµڼ¸ÌìSelectto_char（sysdate,¡¯DD¡¯）fromdual£»0410ÔÂ4ÈÕΪµÚ4Ìì

DÖÜÄÚµÚ¼¸ÌìSelectto_char（sysdate,¡¯D¡¯）fromdual£»52002Äê3ÔÂ14ÈÕΪÐÇÆÚÒ»

DYÖÜÄÚµÚ¼¸ÌìËõдSelectto_char（sysdate,¡¯DY¡¯）fromdual£»SUN2002Äê3ÔÂ24ÈÕΪÐÇÆÚÌì

HH»òHH1212½øÖÆСʱÊýSelectto_char（sysdate,¡¯HH¡¯）fromdual£»02ÎçÒ¹2µã¹ý8·ÖΪ02

HH2424СʱÖÆSelectto_char（sysdate,¡¯HH24¡¯）fromdual£»14ÏÂÎç2µã08·ÖΪ14

MI·ÖÖÓÊý（0¡«59）Selectto_char（sysdate,¡¯MI¡¯）fromdual£»17ÏÂÎç4µã17·Ö

SSÃëÊý（0¡«59）Selectto_char（sysdate,¡¯SS¡¯）fromdual£»2211µã3·Ö22Ãë

ÌáʾעÒâ²»Òª½«MM¸ñʽÓÃÓÚ·ÖÖÓ（·ÖÖÓÓ¦¸ÃʹÓÃMI）¡£MMÊÇÓÃÓÚÔ·ݵĸñʽ£¬½«ËüÓÃÓÚ·ÖÖÓÒ²Äܹ¤×÷£¬µ«½á¹ûÊÇ´íÎóµÄ¡£

ÏÖÔÚ¸ø³öһЩʵ¼ùºóµÄÓ÷¨£º

1¡£ÉÏÔÂÄ©Ì죺

SQL>selectto_char（add_months（last_day（sysdate）,-1）,'yyyy-MM-dd'）LastDayfrom

dual;

LASTDAY

----------

2005-05-31

2¡£ÉÏÔ½ñÌì

SQL>selectto_char（add_months（sysdate,-1）,'yyyy-MM-dd'）PreTodayfromdual;

PRETODAY

----------

2005-05-21

3.ÉÏÔÂÊ×Ìì

SQL>selectto_char（add_months（last_day（sysdate）+1,-2）,'yyyy-MM-dd'）firstDayfromdual;

FIRSTDAY

----------

2005-05-01

4.°´ÕÕÿÖܽøÐÐͳ¼Æ

SQL>selectto_char（sysdate,'ww'）fromdualgroupbyto_char（sysdate,'ww'）;

TO

--

25

5¡£°´ÕÕÿÔ½øÐÐͳ¼Æ

SQL>selectto_char（sysdate,'mm'）fromdualgroupbyto_char（sysdate,'mm'）;

TO

--

06

6¡£°´ÕÕÿ¼¾¶È½øÐÐͳ¼Æ

SQL>selectto_char（sysdate,'q'）fromdualgroupbyto_char（sysdate,'q'）;

T

-

2

7¡£°´ÕÕÿÄê½øÐÐͳ¼Æ

SQL>selectto_char（sysdate,'yyyy'）fromdualgroupbyto_char（sysdate,'yyyy'）;

TO_C

----

2005

8.ÒªÕÒµ½Ä³ÔÂÖÐËùÓÐÖÜÎåµÄ¾ßÌåÈÕÆÚ

selectto_char（t.d,'YY-MM-DD'）from（

selecttrunc（sysdate,'MM'）+rownum-1asd

fromdba_objects

whererownum<32）t

whereto_char（t.d,'MM'）=to_char（sysdate,'MM'）--ÕÒ³öµ±Ç°Ô·ݵÄÖÜÎåµÄÈÕÆÚ

andtrim（to_char（t.d,'Day'））='ÐÇÆÚÎå'

--------

03-05-02

03-05-09

03-05-16

03-05-23

03-05-30

Èç¹û°Ñwhereto_char（t.d,'MM'）=to_char（sysdate,'MM'）¸Ä³Ésysdate-90£¬¼´Îª²éÕÒµ±Ç°Ô·ݵÄÇ°Èý¸öÔÂÖеÄÿÖÜÎåµÄÈÕÆÚ¡£

9.oracleÖÐʱ¼äÔËËã

ÄÚÈÝÈçÏ£º

1¡¢oracleÖ§³Ö¶ÔÈÕÆÚ½øÐÐÔËËã

2¡¢ÈÕÆÚÔËËãʱÊÇÒÔÌìΪµ¥Î»½øÐеÄ

3¡¢µ±ÐèÒªÒÔ·ÖÃëµÈ¸üСµÄµ¥Î»Ëãֵʱ£¬°´Ê±¼ä½øÖƽøÐÐת»»¼´¿É

4¡¢½øÐÐʱ¼ä½øÖÆת»»Ê±×¢Òâ¼ÓÀ¨ºÅ£¬·ñÔò»á³öÎÊÌâ

SQL>altersessionsetnls_date_format='yyyy-mm-ddhh:

mi:

ss';

»á»°ÒѸü¸Ä¡£

SQL>setserverouton

SQL>declare

2DateValuedate;

3begin

4selectsysdateintoDateValuefromdual;

5dbms_output.put_line（'Դʱ¼ä:

'||to_char（DateValue））;

6dbms_output.put_line（'Դʱ¼ä¼õ1Ìì:

'||to_char（DateValue-1））;

7dbms_output.put_line（'Դʱ¼ä¼õ1Ìì1Сʱ:

'||to_char（DateValue-1-1/24））;

8dbms_output.put_line（'Դʱ¼ä¼õ1Ìì1Сʱ1·Ö:

'||to_char（DateValue-1-1/24-1/（24*60）））;

9dbms_output.put_line（'Դʱ¼ä¼õ1Ìì1Сʱ1·Ö1Ãë:

'||to_char（DateValue-1-1/24-1/（24*60）-1/（24*60*60）））;

10end;

11/

Դʱ¼ä:

2003-12-2911:

53:

41

Դʱ¼ä¼õ1Ìì:

2003-12-2811:

53:

41

Դʱ¼ä¼õ1Ìì1Сʱ:

2003-12-2810:

53:

41

Դʱ¼ä¼õ1Ìì1Сʱ1·Ö:

2003-12-2810:

52:

41

Դʱ¼ä¼õ1Ìì1Сʱ1·Ö1Ãë:

2003-12-2810:

52:

40

PL/SQL¹ý³ÌÒѳɹ¦Íê³É¡£

ÔÚOracleÖÐʵÏÖʱ¼äÏà¼Ó´¦Àí

--Ãû³Æ£ºAdd_Times

--¹¦ÄÜ£º·µ»Ød1ÓëNewTimeÏà¼ÓÒÔºóµÄ½á¹û£¬ÊµÏÖʱ¼äµÄÏà¼Ó

--˵Ã÷£º¶ÔÓÚNewTimeÖеÄÈÕÆÚ²»Ó迼ÂÇ

--ÈÕÆÚ£º2004-12-07

--°æ±¾£º1.0

--×÷ÕߣºKevin

createorreplacefunctionAdd_Times（d1indate,NewTimeindate）returndate

is

hhnumber;

mmnumber;

ssnumber;

hoursnumber;

dResultdate;

begin

--ÏÂÃæÒÀ´ÎÈ¡³öʱ¡¢·Ö¡¢Ãë

selectto_number（to_char（NewTime,'HH24'））intohhfromdual;

selectto_number（to_char（NewTime,'MI'））intommfromdual;

selectto_number（to_char（NewTime,'SS'））intossfromdual;

--»»Ëã³öNewTimeÖÐСʱ×ܺͣ¬ÔÚÒ»ÌìµÄ°Ù·Ö¼¸

hours:

=（hh+（mm/60）+（ss/3600））/24;

--µÃ³öʱ¼äÏà¼ÓºóµÄ½á¹û

selectd1+hoursintodResultfromdual;

return（dResult）;

endAdd_Times;

--²âÊÔÓÃÀý

--selectAdd_Times（sysdate,to_date（'2004-12-0603:

23:

00','YYYY-MM-DDHH24:

MI:

SS'））fromdual

ÔÚOracle9iÖмÆËãʱ¼ä²î

¼ÆËãʱ¼ä²îÊÇOracleDATAÊý¾ÝÀàÐ͵ÄÒ»¸ö³£¼ûÎÊÌâ¡£OracleÖ§³ÖÈÕÆÚ¼ÆË㣬Äã¿ÉÒÔ´´½¨ÖîÈç¡°ÈÕÆÚ1£ÈÕÆÚ2¡±ÕâÑùµÄ±í´ïʽÀ´¼ÆËãÕâÁ½¸öÈÕÆÚÖ®¼äµÄʱ¼ä²î¡£

Ò»µ©Äã·¢ÏÖÁËʱ¼ä²îÒ죬Äã¿ÉÒÔʹÓüòµ¥µÄ¼¼ÇÉÀ´ÒÔÌ졢Сʱ¡¢·ÖÖÓ»òÕßÃëΪµ¥Î»À´¼ÆËãʱ¼ä²î¡£ÎªÁ˵õ½Êý¾Ý²î£¬Äã±ØÐëÑ¡ÔñºÏÊʵÄʱ¼ä¶ÈÁ¿µ¥Î»£¬ÕâÑù¾Í¿ÉÒÔ½øÐÐÊý¾Ý¸ñʽÒþ²Ø¡£

ʹÓÃÍêÉƸ´ÔÓµÄת»»º¯ÊýÀ´×ª»»ÈÕÆÚÊÇÒ»¸öÓջ󣬵«ÊÇÄã»á·¢ÏÖÕâ²»ÊÇ×îºÃµÄ½â¾ö·½·¨¡£

round（to_number（end-date-start_date））-ÏûÊŵÄʱ¼ä£¨ÒÔÌìΪµ¥Î»£©

round（to_number（end-date-start_date）*24）-ÏûÊŵÄʱ¼ä£¨ÒÔСʱΪµ¥Î»£©

round（to_number（end-date-start_date）*1440）-ÏûÊŵÄʱ¼ä£¨ÒÔ·ÖÖÓΪµ¥Î»£©

ÏÔʾʱ¼ä²îµÄĬÈÏģʽÊÇʲô£¿ÎªÁËÕÒµ½Õâ¸öÎÊÌâµÄ´ð°¸£¬ÈÃÎÒÃǽøÐÐÒ»¸ö¼òµ¥µÄSQ