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-mm-dd hh24:mi:ss') as newTime from dual?? //改變時間-秒

29.查找月的第一天,最后一天
???? SELECT Trunc(Trunc(SYSDATE, 'MONTH') - 1, 'MONTH') First_Day_Last_Month,
?????? Trunc(SYSDATE, 'MONTH') - 1 / 86400 Last_Day_Last_Month,
?????? Trunc(SYSDATE, 'MONTH') First_Day_Cur_Month,
?????? LAST_DAY(Trunc(SYSDATE, 'MONTH')) + 1 - 1 / 86400 Last_Day_Cur_Month
?? FROM dual;

三. 字符函數(可用于字面字符或數據庫列)

1，字符串截取
?? select substr('abcdef',1,3) from dual

2,查找子串位置
?? select instr('abcfdgfdhd','fd') from dual

3,字符串連接
?? select 'HELLO'||'hello world' from dual;

4, 1)去掉字符串中的空格
??? select ltrim(' abc') s1,
??? rtrim('zhang ') s2,
??? trim(' zhang ') s3 from dual
?? 2)去掉前導和后綴
??? select trim(leading 9 from 9998767999) s1,
??? trim(trailing 9 from 9998767999) s2,
??? trim(9 from 9998767999) s3 from dual;
???
5,返回字符串首字母的Ascii值
?? select ascii('a') from dual

6,返回ascii值對應的字母
?? select chr(97) from dual

7,計算字符串長度?
?? select length('abcdef') from dual

8,initcap（首字母變大寫） ,lower(變小寫),upper(變大寫)
?? select lower('ABC') s1,?
?????? upper('def') s2,?
?????? initcap('efg') s3
?? from dual;

9,Replace
?? select replace('abc','b','xy') from dual;

10,translate
?? select translate('abc','b','xx') from dual; -- x是1位

11,lpad [左添充] rpad [右填充](用于控制輸出格式)
?? select lpad('func',15,'=') s1, rpad('func',15,'-') s2 from dual;
?? select lpad(dname,14,'=') from dept;

12, decode[實現if ..then 邏輯]?? 注:第一個是表達式,最后一個是不滿足任何一個條件的值
?? select deptno,decode(deptno,10,'1',20,'2',30,'3','其他') from dept;
?? 例:
?? select seed,account_name,decode(seed,111,1000,200,2000,0) from t_userInfo//如果seed為111,則取1000;為200,取2000;其它取0
?? select seed,account_name,decode(sign(seed-111),1,'big seed',-1,'little seed','equal seed') from t_userInfo//如果seed>111,則顯示大;為200,則顯示小;其它則顯

示相等

13 case[實現switch ..case 邏輯]
??? SELECT CASE X-FIELD?
???????? WHEN X-FIELD < 40 THEN 'X-FIELD 小于 40'
???????? WHEN X-FIELD < 50 THEN 'X-FIELD 小于 50'
???????? WHEN X-FIELD < 60 THEN 'X-FIELD 小于 60'
???????? ELSE 'UNBEKNOWN'
??????? END
?? FROM DUAL?
???
?? 注:CASE語句在處理類似問題就顯得非常靈活。當只是需要匹配少量數值時，用Decode更為簡潔。

四.數字函數
1，取整函數（ceil 向上取整,floor 向下取整）
?? select ceil(66.6) N1,floor(66.6) N2 from dual;

2, 取冪(power) 和 求平方根(sqrt)
?? select power(3,2) N1,sqrt(9) N2 from dual;

3,求余
?? select mod(9,5) from dual;

4,返回固定小數位數 (round:四舍五入，trunc:直接截斷)
?? select round(66.667,2) N1,trunc(66.667,2) N2 from dual;

5,返回值的符號(正數返回為1,負數為-1)
?? select sign(-32),sign(293) from dual;

五.轉換函數
1，to_char()[將日期和數字類型轉換成字符類型]
?? 1) select to_char(sysdate) s1,
??????? to_char(sysdate,'yyyy-mm-dd') s2,
??????? to_char(sysdate,'yyyy') s3,
??????? to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') s4,
??????? to_char(sysdate, 'hh24:mi:ss') s5,
??????? to_char(sysdate,'DAY') s6?
??? from dual;
?? 2) select sal,to_char(sal,'＄99999') n1,to_char(sal,'＄99,999') n2 from emp

2, to_date()[將字符類型轉換為日期類型]?
??? insert into emp(empno,hiredate) values(8000,to_date('2004-10-10','yyyy-mm-dd'));
???
3, to_number() 轉換為數字類型?
??? select to_number(to_char(sysdate,'hh12')) from dual; //以數字顯示的小時數
???
六.其他函數
?? 1.user:?
??? 返回登錄的用戶名稱?
??? select user from dual;
????
?? 2.vsize:?
??? 返回表達式所需的字節數
??? select vsize('HELLO') from dual;
???
?? 3.nvl(ex1,ex2): 　
??? ex1值為空則返回ex2,否則返回該值本身ex1（常用）?
??? 例：如果雇員沒有傭金，將顯示0，否則顯示傭金?
??? select comm,nvl(comm,0) from emp;
???
?? 4.nullif(ex1,ex2):?
??? 值相等返空，否則返回第一個值
??? 例：如果工資和傭金相等，則顯示空，否則顯示工資
??? select nullif(sal,comm),sal,comm from emp;
???
?? 5.coalesce: 　
??? 返回列表中第一個非空表達式
??? select comm,sal,coalesce(comm,sal,sal*10) from emp;
???
?? 6.nvl2(ex1,ex2,ex3) :
??? 如果ex1不為空，顯示ex2,否則顯示ex3
??? 如：查看有傭金的雇員姓名以及他們的傭金?
??? 　　select nvl2(comm,ename,') as HaveCommName,comm from emp;
???
???
七.分組函數
max min avg count sum
1，整個結果集是一個組
?? 1) 求部門30 的最高工資，最低工資,平均工資，總人數，有工作的人數，工種數量及工資總和
???? select max(ename),max(sal),?
???? min(ename),min(sal),
???? avg(sal),
???? count(*) ,count(job),count(distinct(job)) ,
???? sum(sal) from emp where deptno=30;
2, 帶group by 和 having 的分組
?? 1)按部門分組求最高工資，最低工資，總人數，有工作的人數，工種數量及工資總和
??? select deptno, max(ename),max(sal),
??? min(ename),min(sal),
??? avg(sal),
??? count(*) ,count(job),count(distinct(job)) ,
??? sum(sal) from emp group by deptno;
???
?? 2)部門30的最高工資，最低工資，總人數，有工作的人數，工種數量及工資總和?
??? select deptno, max(ename),max(sal),
??? min(ename),min(sal),
??? avg(sal),
??? count(*) ,count(job),count(distinct(job)) ,
??? sum(sal) from emp group by deptno having deptno=30;
???
3, stddev 返回一組值的標準偏差
??? select deptno,stddev(sal) from emp group by deptno;
??? variance 返回一組值的方差差
??? select deptno,variance(sal) from emp group by deptno;

4, 帶有rollup和cube操作符的Group By
??? rollup 按分組的第一個列進行統計和最后的小計
??? cube 按分組的所有列的進行統計和最后的小計
??? select deptno,job ,sum(sal) from emp group by deptno,job;
??? select deptno,job ,sum(sal) from emp group by rollup(deptno,job);?
??? cube 產生組內所有列的統計和最后的小計
??? select deptno,job ,sum(sal) from emp group by cube(deptno,job);

八、臨時表
?? 只在會話期間或在事務處理期間存在的表.
?? 臨時表在插入數據時，動態分配空間?
?? create global temporary table temp_dept
?? (dno number,
?? dname varchar2(10))
?? on commit delete rows;
?? insert into temp_dept values(10,'ABC');
?? commit;
?? select * from temp_dept; --無數據顯示,數據自動清除
?? on commit preserve rows:在會話期間表一直可以存在（保留數據）
?? on commit delete rows:事務結束清除數據（在事務結束時自動刪除表的數據）