Contents ...
udn網路城邦
Oracle時間日期操作
2008/07/10 00:09
瀏覽17,668
迴響0
推薦0
引用0
Oracle時間日期操作
Oracle時間日期操作
Oracle時間日期操作
 
關鍵字:   Oracle時間日期操作    
 
Oracle時間日期操作
 
sysdate+(5/24/60/60) 在系統時間基礎上延遲5秒
 
sysdate+5/24/60 在系統時間基礎上延遲5分鐘
 
sysdate+5/24 在系統時間基礎上延遲5小時
 
sysdate+5 在系統時間基礎上延遲5天
 
add_months(sysdate,-5) 在系統時間基礎上延遲5月
 
add_months(sysdate,-5*12) 在系統時間基礎上延遲5年
 
 
 
 
 
上月末的日期:select last_day(add_months(sysdate, -1)) from dual;
 
本月的最後一秒:select trunc(add_months(sysdate,1),'MM') - 1/24/60/60 from dual
 
本周星期一的日期:select trunc(sysdate,'day')+1 from dual
 
 
 
 
 
年初至今的天數:select ceil(sysdate - trunc(sysdate, 'year')) from dual;
 
 
 
今天是今年的第幾周 :select to_char(sysdate,'fmww') from dual
 
今天是本月的第幾周:SELECT TO_CHAR(SYSDATE,'WW') - TO_CHAR(TRUNC(SYSDATE,'MM'),'WW') + 1 AS "weekOfMon" FROM dual
 
 
 
本月的天數
 
SELECT to_char(last_day(SYSDATE),'dd') days FROM dual
 
今年的天數
 
select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual
 
下個星期一的日期
 
SELECT Next_day(SYSDATE,'monday') FROM dual
 
 
 
 
 
============================================
 
 
 
--計算工作日方法
 
 
 
create table t(s date,e date);
 
alter session set nls_date_format = 'yyyy-mm-dd';
 
insert into t values('2003-03-01','2003-03-03');
 
insert into t values('2003-03-02','2003-03-03');
 
insert into t values('2003-03-07','2003-03-08');
 
insert into t values('2003-03-07','2003-03-09');
 
insert into t values('2003-03-05','2003-03-07');
 
insert into t values('2003-02-01','2003-03-31');
 
 
 
-- 這裏假定日期都是不帶時間的,否則在所有日期前加trunc即可。
 
select s,e,e-s+1 total_days,
 
trunc((e-s+1)/7)*5 + length(replace(substr('01111100111110',to_char(s,'d'),mod(e-s+1,7)),'0','')) work_days
 
from t;
 
 
 
-- drop table t;
 
 
 
 
 
 
 
 
================================================================================
 
 
 
判斷當前時間是上午下午還是晚上
 
 
 
SELECT CASE
 
WHEN to_number(to_char(SYSDATE,'hh24')) BETWEEN 6 AND 11 THEN '上午'
 
WHEN to_number(to_char(SYSDATE,'hh24')) BETWEEN 11 AND 17 THEN '下午'
 
WHEN to_number(to_char(SYSDATE,'hh24')) BETWEEN 17 AND 21 THEN '晚上'
 
END
 
FROM dual;
 
 
 
 
 
================================================================================
 
 
 
Oracle 中的一些處理日期
 
 
 
 
 
 
 
將數位轉換?任意時間格式.如秒:需要轉換?天/小時
 
SELECT to_char(floor(TRUNC(936000/(60*60))/24))||'天'||to_char(mod(TRUNC(936000/(60*60)),24))||'小時' FROM DUAL
 
 
 
 
 
TO_DATE格式
 
Day:
 
dd number 12
 
dy abbreviated fri
 
day spelled out friday
 
ddspth spelled out, ordinal twelfth
 
Month:
 
mm number 03
 
mon abbreviated mar
 
month spelled out march
 
Year:
 
yy two digits 98
 
yyyy four digits 1998
 
 
 
24小時格式下時間範圍?: 0:00:00 - 23:59:59....
 
12小時格式下時間範圍?: 1:00:00 - 12:59:59 ....
 
1.
 
日期和字元轉換函數用法(to_date,to_char)
 
 
 
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- <br /> 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.
 
獲得小時數
 
 
 
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) 作?月
 
mod(mod(date2-date1, 365), 30)作?日.
 
23.next_day函數
 
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 dual
 
日期 返回的是天 然後 轉換?ss
 
 
 
 
 
 
 
 
 
 
將數位轉換?任意時間格式.如秒:需要轉換?天/小時
 
SELECT to_char(floor(TRUNC(936000/(60*60))/24))||'天'||to_char(mod(TRUNC(936000/(60*60)),24))||'小時' FROM DUAL
 
 
 
 
 
TO_DATE格式
 
Day:
 
dd number 12
 
dy abbreviated fri
 
day spelled out friday
 
ddspth spelled out, ordinal twelfth
 
Month:
 
mm number 03
 
mon abbreviated mar
 
month spelled out march
 
Year:
 
yy two digits 98
 
yyyy four digits 1998
 
 
 
24小時格式下時間範圍?: 0:00:00 - 23:59:59....
 
12小時格式下時間範圍?: 1:00:00 - 12:59:59 ....
 
1.
 
日期和字元轉換函數用法(to_date,to_char)
 
 
 
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- <br /> 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.
 
獲得小時數
 
 
 
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的區別
 
全站分類:休閒生活 生活情報
自訂分類:DBMS
你可能會有興趣的文章:
發表迴響

會員登入