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,203
2010-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,cb
12-FEB-10 01.24.52.123456000 PM Typ=180 Len=11: 78,6e,2,c,e,19,35,7,5b,ca,0
SQL> 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:16
2.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,ææå
=7
2 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 07
3.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 5
3.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-28
3.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') newdate2
3 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') newdate2
3 from dual;
NEWDATE1 NEWDATE2
----------- ------------------------
2010-2-13 2010-2-12 ä¸å 01:00:00
温馨提示:答案为网友推荐,仅供参考