ããORACLEæ¥ææ¶é´å½æ°å¤§å
¨
ããTO_DATEæ ¼å¼(以æ¶é´:2007-11-02 13:45:25为ä¾)
ããYear:
ããyy two digits 两ä½å¹´ æ¾ç¤ºå¼:07
ããyyy three digits ä¸ä½å¹´ æ¾ç¤ºå¼:007
ããyyyy four digits åä½å¹´ æ¾ç¤ºå¼:2007
ããMonth:
ããmm number 两ä½æ æ¾ç¤ºå¼:11
ããmon abbreviated å符é表示 æ¾ç¤ºå¼:11æ,è¥æ¯è±æç,æ¾ç¤ºnov
ããmonth spelled out å符é表示 æ¾ç¤ºå¼:11æ,è¥æ¯è±æç,æ¾ç¤ºnovember
ããDay:
ããdd number å½æ第å 天 æ¾ç¤ºå¼:02
ããddd number å½å¹´ç¬¬å 天 æ¾ç¤ºå¼:02
ããdy abbreviated å½å¨ç¬¬å 天ç®å æ¾ç¤ºå¼:ææäº,è¥æ¯è±æç,æ¾ç¤ºfri
ããday spelled out å½å¨ç¬¬å 天å
¨å æ¾ç¤ºå¼:ææäº,è¥æ¯è±æç,æ¾ç¤ºfriday
ããddspth spelled out, ordinal twelfth
ããHour:
ããhh two digits 12å°æ¶è¿å¶ æ¾ç¤ºå¼:01
ããhh24 two digits 24å°æ¶è¿å¶ æ¾ç¤ºå¼:13
ããMinute:
ããmi two digits 60è¿å¶ æ¾ç¤ºå¼:45
ããSecond:
ããss two digits 60è¿å¶ æ¾ç¤ºå¼:25
ããå
¶å®
ããQ digit å£åº¦ æ¾ç¤ºå¼:4
ããWW digit å½å¹´ç¬¬å å¨ æ¾ç¤ºå¼:44
ããW digit å½æ第å å¨ æ¾ç¤ºå¼:1
ãã24å°æ¶æ ¼å¼ä¸æ¶é´èå´ä¸ºï¼ 0:00:00 - 23:59:59....
ãã12å°æ¶æ ¼å¼ä¸æ¶é´èå´ä¸ºï¼ 1:00:00 - 12:59:59 ....
ãã1. æ¥æåå符转æ¢å½æ°ç¨æ³ï¼to_date,to_charï¼
ããselect to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual; //æ¥æ转å为å符串
ããselect to_char(sysdate,'yyyy') as nowYear from dual; //è·åæ¶é´çå¹´
ããselect to_char(sysdate,'mm') as nowMonth from dual; //è·åæ¶é´çæ
ããselect to_char(sysdate,'dd') as nowDay from dual; //è·åæ¶é´çæ¥
ããselect to_char(sysdate,'hh24') as nowHour from dual; //è·åæ¶é´çæ¶
ããselect to_char(sysdate,'mi') as nowMinute from dual; //è·åæ¶é´çå
ããselect to_char(sysdate,'ss') as nowSecond from dual; //è·åæ¶é´çç§
ããselect to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss') from dual//
ãã2.
ããselect to_char( to_date(222,'J'),'Jsp') from dual
ããæ¾ç¤ºTwo Hundred Twenty-Two
ãã3.æ±æ天æ¯ææå
ããselect to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;
ããææä¸
ããselect to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
ããmonday
ãã设置æ¥æè¯è¨
ããALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
ããä¹å¯ä»¥è¿æ ·
ããTO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')
ãã4. 两个æ¥æé´ç天æ°
ããselect floor(sysdate - to_date('20020405','yyyymmdd')) from dual;
ãã5. æ¶é´ä¸ºnullçç¨æ³
ããselect id, active_date from table1
ããUNION
ããselect 1, TO_DATE(null) from dual;
ãã注æè¦ç¨TO_DATE(null)
ãã6.æ份差
ããa_date between to_date('20011201','yyyymmdd') and to_date('20011231','yyyymmdd')
ããé£ä¹12æ31å·ä¸å12ç¹ä¹åå12æ1å·ç12ç¹ä¹åæ¯ä¸å
å«å¨è¿ä¸ªèå´ä¹å
çã
ããæ以ï¼å½æ¶é´éè¦ç²¾ç¡®çæ¶åï¼è§å¾to_charè¿æ¯å¿
è¦ç
ãã7. æ¥ææ ¼å¼å²çªé®é¢
ããè¾å
¥çæ ¼å¼è¦çä½ å®è£
çORACLEå符éçç±»å, æ¯å¦: US7ASCII, dateæ ¼å¼çç±»åå°±æ¯: '01-Jan-01'
ããalter system set NLS_DATE_LANGUAGE = American
ããalter session set NLS_DATE_LANGUAGE = American
ããæè
å¨to_dateä¸å
ããselect to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
ãã注ææè¿åªæ¯ä¸¾äºNLS_DATE_LANGUAGEï¼å½ç¶è¿æå¾å¤ï¼
ããå¯æ¥ç
ããselect * from nls_session_parameters
ããselect * from V$NLS_PARAMETERS
ãã8.
ããselect count(*)
ããfrom ( select rownum-1 rnum
ããfrom all_objects
ããwhere rownum <= to_date('2002-02-28','yyyy-mm-dd') - to_date('2002-
ãã02-01','yyyy-mm-dd')+1
ãã)
ããwhere to_char( to_date('2002-02-01','yyyy-mm-dd')+rnum-1, 'D' )
ããnot in ( '1', '7' )
ããæ¥æ¾2002-02-28è³2002-02-01é´é¤ææä¸åä¸ç天æ°
ããå¨åååå«è°ç¨DBMS_UTILITY.GET_TIME, 让åå°ç»æç¸å(å¾å°çæ¯1/100ç§, èä¸æ¯æ¯«ç§).
ãã9. æ¥æ¾æ份
ããselect months_between(to_date('01-31-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;
ãã1
ããselect months_between(to_date('02-01-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;
ãã1.03225806451613
ãã10. Next_dayçç¨æ³
ããNext_day(date, day)
ããMonday-Sunday, for format code DAY
ããMon-Sun, for format code DY
ãã1-7, for format code D
ãã11
ããselect to_char(sysdate,'hh:mi:ss') TIME from all_objects
ãã注æï¼ç¬¬ä¸æ¡è®°å½çTIME ä¸æåä¸è¡æ¯ä¸æ ·ç
ããå¯ä»¥å»ºç«ä¸ä¸ªå½æ°æ¥å¤çè¿ä¸ªé®é¢
ããcreate or replace function sys_date return date is
ããbegin
ããreturn sysdate;
ããend;
ããselect to_char(sys_date,'hh:mi:ss') from all_objects;
ãã12.è·å¾å°æ¶æ°
ããextract()æ¾åºæ¥ææé´éå¼çå段å¼
ããSELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 2:38:40') from offer
ããSQL> select sysdate ,to_char(sysdate,'hh') from dual;
ããSYSDATE TO_CHAR(SYSDATE,'HH')
ãã-------------------- ---------------------
ãã2003-10-13 19:35:21 07
ããSQL> select sysdate ,to_char(sysdate,'hh24') from dual;
ããSYSDATE TO_CHAR(SYSDATE,'HH24')
ãã-------------------- -----------------------
ãã2003-10-13 19:35:21 19
ãã13.å¹´ææ¥çå¤ç
ããselect older_date,
ããnewer_date,
ããyears,
ããmonths,
ããabs(
ããtrunc(
ããnewer_date-
ããadd_months( older_date,years*12+months )
ãã)
ãã) days
ããfrom ( select
ããtrunc(months_between( newer_date, older_date )/12) YEARS,
ããmod(trunc(months_between( newer_date, older_date )),12 ) MONTHS,
ããnewer_date,
ããolder_date
ããfrom (
ããselect hiredate older_date, add_months(hiredate,rownum)+rownum newer_date
ããfrom emp
ãã)
ãã)
ãã14.å¤çæ份天æ°ä¸å®çåæ³
ããselect to_char(add_months(last_day(sysdate) +1, -2), 'yyyymmdd'),last_day(sysdate) from dual
ãã16.æ¾åºä»å¹´ç天æ°
ããselect add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual
ããé°å¹´çå¤çæ¹æ³
ããto_char( last_day( to_date('02' | | :year,'mmyyyy') ), 'dd' )
ããå¦ææ¯28å°±ä¸æ¯é°å¹´
ãã17.yyyyä¸rrrrçåºå«
ãã'YYYY99 TO_C
ãã------- ----
ããyyyy 99 0099
ããrrrr 99 1999
ããyyyy 01 0001
ããrrrr 01 2001
ãã18.ä¸åæ¶åºçå¤ç
ããselect to_char( NEW_TIME( sysdate, 'GMT','EST'), 'dd/mm/yyyy hh:mi:ss') ,sysdate
ããfrom dual;
ãã19.5ç§éä¸ä¸ªé´é
ããSelect TO_DATE(FLOOR(TO_CHAR(sysdate,'SSSSS')/300) * 300,'SSSSS') ,TO_CHAR(sysdate,'SSSSS')
ããfrom dual
ãã2002-11-1 9:55:00 35786
ããSSSSS表示5ä½ç§æ°
ãã20.ä¸å¹´ç第å 天
ããselect TO_CHAR(SYSDATE,'DDD'),sysdate from dual
ãã310 2002-11-6 10:03:51
ãã21.计ç®å°æ¶,å,ç§,毫ç§
ããselect
ããDays,
ããA,
ããTRUNC(A*24) Hours,
ããTRUNC(A*24*60 - 60*TRUNC(A*24)) Minutes,
ããTRUNC(A*24*60*60 - 60*TRUNC(A*24*60)) Seconds,
ããTRUNC(A*24*60*60*100 - 100*TRUNC(A*24*60*60)) mSeconds
ããfrom
ãã(
ããselect
ããtrunc(sysdate) Days,
ããsysdate - trunc(sysdate) A
ããfrom dual
ãã)
ããselect * from tabname
ããorder by decode(mode,'FIFO',1,-1)*to_char(rq,'yyyymmddhh24miss');
ãã//
ããfloor((date2-date1) /365) ä½ä¸ºå¹´
ããfloor((date2-date1, 365) /30) ä½ä¸ºæ
ããd(mod(date2-date1, 365), 30)ä½ä¸ºæ¥.
ãã23.next_dayå½æ° è¿åä¸ä¸ªææçæ¥æ,day为1-7ææææ¥-ææå
,1表示æææ¥
ããnext_day(sysdate,6)æ¯ä»å½åå¼å§ä¸ä¸ä¸ªææäºãåé¢çæ°åæ¯ä»æææ¥å¼å§ç®èµ·ã
ãã1 2 3 4 5 6 7
ããæ¥ ä¸ äº ä¸ å äº å
ãã---------------------------------------------------------------
ããselect (sysdate-to_date('2003-12-03 12:55:45','yyyy-mm-dd hh24:mi:ss'))*24*60*60 from ddual
ããæ¥æ è¿åçæ¯å¤© ç¶å 转æ¢ä¸ºss
ãã24,round[èå
¥å°ææ¥è¿çæ¥æ](day:èå
¥å°ææ¥è¿çæææ¥)
ããselect sysdate S1,
ããround(sysdate) S2 ,
ããround(sysdate,'year') YEAR,
ããround(sysdate,'month') MONTH ,
ããround(sysdate,'day') DAY from dual
ãã25,trunc[æªæå°ææ¥è¿çæ¥æ,åä½ä¸ºå¤©] ,è¿åçæ¯æ¥æç±»å
ããselect sysdate S1,
ããtrunc(sysdate) S2, //è¿åå½åæ¥æ,æ æ¶åç§
ããtrunc(sysdate,'year') YEAR, //è¿åå½åå¹´ç1æ1æ¥,æ æ¶åç§
ããtrunc(sysdate,'month') MONTH , //è¿åå½åæç1æ¥,æ æ¶åç§
ããtrunc(sysdate,'day') DAY //è¿åå½åææçææ天,æ æ¶åç§
ããfrom dual
ãã26,è¿åæ¥æå表ä¸æææ¥æ
ããselect greatest('01-1æ-04','04-1æ-04','10-2æ-04') from dual
ãã27.计ç®æ¶é´å·®
ãã注:oracleæ¶é´å·®æ¯ä»¥å¤©æ°ä¸ºåä½,æ以æ¢ç®æå¹´æ,æ¥
ããselect floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))/365) as spanYears from dual //æ¶é´å·®-å¹´
ããselect ceil(moths_between(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanMonths from dual //æ¶é´å·®-æ
ããselect floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanDays from dual //æ¶é´å·®-天
ããselect floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24) as spanHours from dual //æ¶é´å·®-æ¶
ããselect floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60) as spanMinutes from dual //æ¶é´å·®-å
ããselect floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60*60) as spanSeconds from dual //æ¶é´å·®-ç§
ãã28.æ´æ°æ¶é´
ãã注:oracleæ¶é´å åæ¯ä»¥å¤©æ°ä¸ºåä½,设æ¹åé为n,æ以æ¢ç®æå¹´æ,æ¥
ããselect to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n*365,'yyyy-mm-dd hh24:mi:ss') as newTime from dual //æ¹åæ¶é´-å¹´
ããselect to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),add_months(sysdate,n) as newTime from dual //æ¹åæ¶é´-æ
ããselect to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n,'yyyy-mm-dd hh24:mi:ss') as newTime from dual //æ¹åæ¶é´-æ¥
ããselect to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24,'yyyy-mm-dd hh24:mi:ss') as newTime from dual //æ¹åæ¶é´-æ¶
ããselect to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24/60,'yyyy-mm-dd hh24:mi:ss') as newTime from dual //æ¹åæ¶é´-å
ããselect to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24/60/60,'yyyy-
温馨提示:答案为网友推荐,仅供参考