ããoracle æ°æ®ç±»å详解---æ¥æå
ããoracleæ°æ®ç±»åçèµ·æ¥é常ç®åï¼ä½ç¨èµ·æ¥ä¼åç°æ许å¤ç¥è¯ç¹ï¼æ¬ææ¯æ对ORACLEæ¥ææ°æ®ç±»åçä¸äºæ´çï¼é½æ¯å¼åå
¥é¨èµæï¼ä¸å¤§å®¶å享ï¼
ãã注ï¼ç±äºINTERVALåTIME ZONEå®é
ç¨å¾æ¯è¾å°ï¼æ以æ¬æå
容æªæ¶åè¿ä¸¤ä¸ªæ¹é¢ã
ãã1ã常ç¨æ¥æåæ°æ®ç±»å
ãã1.1ãDATE
ããè¿æ¯ORACLEæ常ç¨çæ¥æç±»åï¼å®å¯ä»¥ä¿åæ¥æåæ¶é´ï¼å¸¸ç¨æ¥æå¤çé½å¯ä»¥éç¨è¿ç§ç±»åãDATE表示çæ¥æèå´å¯ä»¥æ¯å
Œ
å4712å¹´1æ1æ¥è³å
Œ
9999å¹´12æ31æ¥dateç±»åå¨æ°æ®åºä¸çåå¨åºå®ä¸º7个åèï¼æ ¼å¼ä¸ºï¼
ãã第1åèï¼ä¸çºª+100
ãã第2åèï¼å¹´
ãã第3åèï¼æ
ãã第4åèï¼å¤©
ãã第5åèï¼å°æ¶+1
ãã第6åèï¼å+1
ãã第7åèï¼ç§+1
ãã1.2ãTIMESTAMP(p)
ããè¿ä¹æ¯ORACLE常ç¨çæ¥æç±»åï¼å®ä¸dateçåºå«æ¯ä¸ä»
å¯ä»¥ä¿åæ¥æåæ¶é´ï¼è¿è½ä¿åå°æ°ç§ï¼å°æ°ä½æ°å¯ä»¥æå®ä¸º0-9ï¼é»è®¤ä¸º6ä½ï¼æ以æé«ç²¾åº¦å¯ä»¥å°ns(纳ç§)ï¼æ°æ®åºå
é¨ç¨7æè
11个åèåå¨ï¼å¦æ精度为0ï¼åç¨7åèåå¨ï¼ä¸dateç±»ååè½ç¸åï¼å¦æ精度大äº0åç¨11åèåå¨ã
ããæ ¼å¼ä¸ºï¼
ãã第1åèï¼ä¸çºª+100
ãã第2åèï¼å¹´
ãã第3åèï¼æ
ãã第4åèï¼å¤©
ãã第5åèï¼å°æ¶+1
ãã第6åèï¼å+1
ãã第7åèï¼ç§+1
ãã第8-11åèï¼çº³ç§ï¼éç¨4个åèåå¨ï¼å
é¨è¿ç®ç±»å为æ´å½¢æ³¨ï¼TIMESTAMPæ¥æç±»åå¦æä¸æ°å¼è¿è¡å åè¿ç®ä¼èªå¨è½¬æ¢ä¸ºDATEåï¼ä¹å°±æ¯è¯´å°æ°ç§ä¼èªå¨å»é¤ã
ãã1.3ãDATEä¸TIMESTAMPç±»åå
é¨åå¨éªè¯
ããå¤å¶ä»£ç
ãã1 create table T
ãã2 (
ãã3 C1 DATE,
ãã4 C2 TIMESTAMP(9)
ãã5 );
ãã6
ãã7 insert into t(c1,c2) values(date'2010-2-12',timestamp'2010-2-12 13:24:52.234123211');8 insert into t(c1,c2) values(
ãã9 to_date('2010-2-12 10:20:30','YYYY-MM-DD HH24:MI:SS'),10 to_timestamp('2010-2-12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6')11 );
ãã12
ãã13 SQL> select c1,dump(c1) c1_d,c2,dump(c2) c2_d from t;å¤å¶ä»£ç
ããC1 C1_D C2 C2_D------------------------ ---------------------------------------- ---------------------------------------- -----------------------------------------------------2010-2-12 Typ=12 Len=7: 120,110,2,12,1,1,1 12-FEB-10 01.24.52.234123211 PM Typ=180 Len=11: 120,110,2,12,14,25,53,13,244,111,2032010-2-12 ä¸å 10:20:30 Typ=12 Len=7: 120,110,2,12,11,21,31 12-FEB-10 01.24.52.123456000 PM Typ=180 Len=11: 120,110,2,12,14,25,53,7,91,202,0以ä¸æ¯ä¸ºäºæµè¯æ¯ä¸ºäºéªè¯TIMESTAMPçå°æ°ä½åå¨ç®æ³ï¼
ãã1 SQL> select c2,dump(c2,16) c2_d16 from t;C2 C2_D16---------------------------------------- --------------------------------------------------------------------------------12-FEB-10 01.24.52.234123211 PM Typ=180 Len=11: 78,6e,2,c,e,19,35,d,f4,6f,cb12-FEB-10 01.24.52.123456000 PM Typ=180 Len=11: 78,6e,2,c,e,19,35,7,5b,ca,0SQL> select to_number('0df46fcb','xxxxxxxx') mydata1,to_number('075bca00','xxxxxxxx') mydata2 from dual;MYDATA1 MYDATA2
ãã---------- ----------
ãã234123211 123456000
ãã2ã常è§é®é¢
ãã2.1ãå¦ä½åå½åæ¶é´
ããsysdate--è¿åå½åç³»ç»æ¥æåæ¶é´ï¼ç²¾ç¡®å°ç§systimestamp--è¿åå½åç³»ç»æ¥æåæ¶é´ï¼ç²¾ç¡®å°æ¯«ç§2.2ãå¦ä½è¿è¡æ¥æè¿ç®
ããæ¥æåæ°æ®å¯ä»¥ä¸æ°å¼å åå¾å°æ°çæ¥æï¼å åæ°å¼åä½ä¸ºå¤©sysdate+1--åæ天çå½åæ¶é´
ããsysdate-1/24--åå½åæ¶é´çåä¸ä¸ªå°æ¶
ããSQL> select sysdate d1,sysdate+1 d2,sysdate-1/24 d3 from dual;D1 D2 D3------------------------ ------------------------ ------------------------2010-5-13 ä¸å 10:55:16 2010-5-14 ä¸å 10:55:16 2010-5-13 ä¸å 09:55:162.3ãå¦ä½æ±ä¸¤ä¸ªæ¥æçé´éæ¶é´
ããå¯ä»¥ç´æ¥æ两个æ¥æç¸åï¼è¿åçåä½ä¸ºå¤©ï¼å°æ¶ååç§ä¼æ¢ç®æå°æ°SQL> select date'2012-01-01'-sysdate from dual;DATE'2012-01-01'-SYSDATE
ãã------------------------
ãã597.046030092593
ãã2.4ãå¦ä½å°æ¥æ转å符
ããto_char(sysdate,'YYYY-MM-DD HH24:MI:SS')
ãã2.5ãå¦ä½å°å符转æ¥æ
ããto_date('2010-02-24 15:01:54','YYYY-MM-DD HH24:MI:SS')to_timestamp('1999-12-01 11:00:00.123456','YYYY-MM-DD HH:MI:SS.FF6')3ã常ç¨æ¥æå½æ°
ãã3.1ãTO_CHAR(DATE,FORMATSTR)--æ ¼å¼åæ¥ææå符SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') d1 from dual;D1
ãã------------------------
ãã2010-05-13 22:56:38
ããTO_CHARçå
¶å®ç¨æ³ç¤ºä¾
ããå¤å¶ä»£ç
ãã1 SQL> SELECT TO_CHAR(date '2010-02-12', 'D') week_dayth,--å¨ç¬¬å 天(1-7)ï¼ææ天=1ï¼ææä¸=2,ææäº=3ï¼ææä¸=4,ææå=5ï¼ææäº=6,ææå
=72 TO_CHAR(date '2010-02-12', 'DD') month_dayth,--æ第å 天3 TO_CHAR(date '2010-02-12', 'DDD') year_dayth,--年第å 天4 TO_CHAR(date '2010-02-12', 'DAY') weekdayname,--è±æææå5 _CHAR(date '2010-02-12', 'w') month_weekth,--æ第å å¨(0-4)6 TO_CHAR(date '2010-02-12', 'ww') year_weekth--年第å å¨(0-53)7 FROM DUAL;
ããå¤å¶ä»£ç
ããWEEK_DAYTH MONTH_DAYTH YEAR_DAYTH WEEKDAYNAME MONTH_WEEKTH YEAR_WEEKTH---------- ----------- ---------- ----------- ------------ -----------6 12 043 FRIDAY 2 073.2ãTO_DATE(CHAR,FORMATSTR) --å°å符转æ¢ææ¥æto_date('2010-02-24 15:01:54','YYYY-MM-DD HH24:MI:SS')æ ¼å¼å¤æ³¨ï¼
ããHH表示12å°æ¶è¿å¶ï¼HH24表示éç¨24å°æ¶è¿å¶ï¼MM表示æ份ï¼MI表示åéã
ãã3.3ãTRUNC(DATE)--è¿åDATEçæ¥æé¨åï¼æ¶é´ä¸º0ç¹0å0ç§SQL> select sysdate d1,trunc(sysdate) d2 from dual;D1 D2
ãã------------------------ ------------------------2010-5-13 ä¸å 10:59:18 2010-5-13
ãã3.4ãEXTRACT(DATA FROM DATEVALUE)--è¿åDATEçæä¸é¨ä»½å
容å¦æDATEVALUE为DATEç±»åï¼åDATAå¯ä»¥æ¯(YEARãMONTHãDAY)å¦æDATEVALUE为TIMESTAMPç±»åï¼åDATAå¯ä»¥æ¯(YEARãMONTH,DAYãHOURãMINUTEãSECOND)SQL> select sysdate d1,EXTRACT(YEAR FROM sysdate) thisyear,EXTRACT(MINUTE FROM systimestamp) thism from dual;D1 THISYEAR THISM------------------------ ---------- ----------2010-5-13 ä¸å 11:05:06 2010 53.5ãADD_MONTHS(DATE,MONTHS) --å¨DATEå¢å æ份å¾å°æ°æ¥æADD_MONTHS(sysdate,3) --å½åæ¥æå 3个æ
ããADD_MONTHSæ¯ä¸ä¸ªæ¯è¾æææçå½æ°ï¼å®ä¼èªå¨å¤ç大å°æåé°æï¼å¦ä¸æ示1 SQL> select ADD_MONTHS(date '2010-2-12', 1),2 ADD_MONTHS(date '2010-2-27', 1),
ãã3 ADD_MONTHS(date '2010-2-28', 1),
ãã4 ADD_MONTHS(date '2010-1-31', 1)
ãã5 from dual
ãã6 ;
ããADD_MONTHS(DATE'2010-2-12',1) ADD_MONTHS(DATE'2010-2-27',1) ADD_MONTHS(DATE'2010-2-28',1) ADD_MONTHS(DATE'2010-1-31',1)----------------------------- ----------------------------- ----------------------------- -----------------------------2010-3-12 2010-3-27 2010-3-31 2010-2-283.6ãLAST_DAY(DATE)--è¿åæ¥ææå¨æ份çæåä¸å¤©æ¥æSQL> select LAST_DAY(date '2010-2-12') from dual;LAST_DAY(DATE'2010-2-12')
ãã-------------------------
ãã2010-2-28
ãã3.7ãNEXT_DAY(DATE,CHAR) --ä»ç»å®æ¥æå¼å§è¿åä¸ä¸ªCHARæå®ææçæ¥æSQL> SELECT NEXT_DAY(date'2010-2-21', 'MONDAY') NEXTDAY1,NEXT_DAY(date'2010-2-22', 'MONDAY') NEXTDAY2 FROM DUAL;NEXTDAY1 NEXTDAY2
ãã----------- -----------
ãã2010-2-22 2010-3-1
ããTO_YMINTERVAL(CHAR)--è¿å[å¹´-æ]æ ¼å¼ææçæ¶é´é´éï¼ä¸è¬ç¨äºæ¥æå åè¿ç®3.8ãTO_DSINTERVAL(CHAR)--è¿å[天 æ¶:å:ç§]æ ¼å¼ææçæ¶é´é´éï¼ä¸è¬ç¨äºæ¥æå åè¿ç®SQL> select date'2010-2-12'+TO_YMINTERVAL('01-02') newdate from dual;NEWDATE
ãã------------------------------
ãã2011-4-12
ãã3.9ãNUMTOYMINTERVAL(N,CHAR) --è¿åCHARä¸æå®åä½çæ¶é´é´éæ°å¼ï¼ä¸è¬ç¨äºæ¥æå åè¿ç®charå¯ä»¥ä¸ºYEAR,MONTH
ãã1 SQL> select date '2010-2-12' + NUMTOYMINTERVAL(1, 'year') newdate1,2 date '2010-2-12' + NUMTOYMINTERVAL(1, 'month') newdate23 from dual;
ããNEWDATE1 NEWDATE2
ãã----------- -----------
ãã2011-2-12 2010-3-12
ãã3.10ãNUMTODSINTERVAL(N,CHAR) --è¿åCHARä¸æå®åä½çæ¶é´é´éæ°å¼ï¼ä¸è¬ç¨äºæ¥æå åè¿ç®charå¯ä»¥ä¸ºDAY,HOUR,MINUTE,SECOND
ãã1 SQL> select date '2010-2-12' + NUMTODSINTERVAL(1, 'DAY') newdate1,2 date '2010-2-12' + NUMTODSINTERVAL(1, 'HOUR') newdate23 from dual;
温馨提示:答案为网友推荐,仅供参考