在oracle數(shù)據(jù)庫的開發(fā)中，常因為時間的問題大費周章，所以特地將ORACLE數(shù)據(jù)的日期函數(shù)收藏致此。乃供他日所查也。
add_months(d,n) 日期d加n個月
last_day(d) 包含d的月?的最后一天的日期
new_time(d,a,b) a?區(qū)的日期和??d在b?區(qū)的日期和??
next_day(d,day) 比日期d?，由day指定的周幾的日期
sysdate 當前的系?日期和??
greatest(d1,d2,...dn) ?出的日期列表中最后的日期
least(d1,k2,...dn) ?出的日期列表中最早的日期
to_char(d [,fmt]) 日期d按fmt指定的格式??成字符串
to_date(st [,fmt]) 字符串st按fmt指定的格式?成日期?，若fmt忽略，st要用缺省格式
round(d [,fmt]) 日期d按fmt指定格式舍入到最近的日期
trunc(d [,fmt]) 日期d按fmt指定格式截斷到最近的日期
to_date 字符串類型轉(zhuǎn)為換日期類型
字符串中的相應(yīng)位置上的字符,必須符合時間范圍的限制

查詢Oracle日期格式
----------------------------------


select * from nls_database_parameters;


得到結(jié)果如下表: 表中NLS_DATE_FORMAT表示日期格式.
PARAMETER VALUE
----------------------------------- -----------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET ZHS16GBK
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_NCHAR_CHARACTERSET ZHS16GBK
NLS_RDBMS_VERSION 8.1.7.0.0

或者查詢V$NLS_PARAMETERS表,
select * from V$NLS_PARAMETERS;
也有類似結(jié)果

SQL>select to_date('2004-11-12 12-07-32','yyyy-mm-dd hh24-mi-ss') value from dual;
VALUE
-------------------
2004.11.12 12:07:32

SQL>select to_date('20041015') value from dual;
VALUE
-------------------
2004.10.15 00:00:00

SQL>select to_date('20041315') value from dual;
ERROR 位于第 1 行:
ORA-01861: 文字與格式字符串不匹配


sysdate 當前日期和時間

SQL>select sysdate value from dual;
VALUE
-------------------
2003.11.23 17:09:01


last_day 本月最后一天

SQL>select last_day(sysdate) value from dual;
VALUE
-------------------
2003.11.30 17:08:17


add_months(d,n) 日期d后推n個月

SQL>select add_months(sysdate,2) value from dual;
VALUE
-------------------
2005.01.23 17:10:21


next_day(d,day) 日期d之后的第一周中,指定的那天(指定星期的第幾天)是什么日期

SQL>select next_day(sysdate,1) value from dual;
VALUE
-------------------
2004.11.28 17:38:55




[oracle/plsql]oracle日期處理完全版

日期處理完全版
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.
日期和字符轉(zhuǎn)換函數(shù)用法（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
設(shè)置日期語言
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
也可以這樣
TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')

4.
兩個日期間的天數(shù)
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點之前是不包含在這個范圍之內(nèi)的。
所以，當時間需要精確的時候，覺得to_char還是必要的
7. 日期格式?jīng)_突問題
輸入的格式要看你安裝的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間除星期一和七的天數(shù)
在前后分別調(diào)用DBMS_UTILITY.GET_TIME, 讓后將結(jié)果相減(得到的是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 與最后一行是一樣的
可以建立一個函數(shù)來處理這個問題
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.
獲得小時數(shù)

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.
處理月份天數(shù)不定的辦法
select to_char(add_months(last_day(sysdate) +1, -2), 'yyyymmdd'),last_day(sysdate) from dual

16.
找出今年的天數(shù)
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的區(qū)別
'YYYY99 TO_C
------- ----
yyyy 99 0099
rrrr 99 1999
yyyy 01 0001
rrrr 01 2001

18.不同時區(qū)的處理
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位秒數(shù)

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函數(shù)
next_day(sysdate,6)是從當前開始下一個星期五。后面的數(shù)字是從星期日開始算起。

很簡單的一句話。
把Oracle的日期當作一個特殊數(shù)字，以天為單位。
可以進行 日期+數(shù)字 = 日期， 日期-日期=數(shù)字，日期-數(shù)字=日期

MESTAMP數(shù)據(jù)的格式化顯示和DATE 數(shù)據(jù)一樣。注意，to_char函數(shù)支持date和timestamp，但是trunc卻不支持TIMESTAMP數(shù)據(jù)類型。這已經(jīng)清楚表明了在當兩個時間的差別極度重要的情況下，使用TIMESTAMP數(shù)據(jù)類型要比DATE數(shù)據(jù)類型更確切。
　　
　　如果你想顯示TIMESTAMP的小數(shù)秒信息，參考下面：
　　
　　1　SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS:FF3') "Date" FROM date_table
　　
　　Date
　　
　　-----------------------
　　
　　06/20/2003 16:55:14:000
　　
　　06/26/2003 11:16:36:000
　　
　　在上例中，我只現(xiàn)實了小數(shù)點后3位的內(nèi)容。
　　
　　計算timestamp間的數(shù)據(jù)差別要比老的date數(shù)據(jù)類型更容易。當你直接相減的話，看看會發(fā)生什么。結(jié)果將更容易理解，第一行的17天，18小時，27分鐘和43秒。
　　
　　1　SELECT time1,
　　
　　　2　　　　 time2,
　　
　　　3　　　　 substr((time2-time1),instr((time2-time1),' ')+7,2)　　　　　　　　 seconds,
　　
　　　4　　　　 substr((time2-time1),instr((time2-time1),' ')+4,2)　　　　　　　　 minutes,
　　
　　　5　　　　 substr((time2-time1),instr((time2-time1),' ')+1,2)　　　　　　　　 hours,
　　
　　　6　　　　 trunc(to_number(substr((time2-time1),1,instr(time2-time1,' '))))　 days,
　　
　　　7　　　　 trunc(to_number(substr((time2-time1),1,instr(time2-time1,' ')))/7) weeks
　　
　　　8*　 FROM date_table
　　
　　TIME1　　　　　　　　　　　 TIME2　　　　　　　　　　　SECONDS MINUTES HOURS DAYS WEEKS
　　
　　-------------------------　 -------------------------- ------- ------- ----- ---- -----
　　
　　06/20/2003:16:55:14:000000　07/08/2003:11:22:57:000000　43　　 27　　　18　　17　　2
　　
　　06/26/2003:11:16:36:000000　07/08/2003:11:22:57:000000　21　　 06　　　00　　12　　1
　　
　　這就意味著不再需要關(guān)心一天有多少秒在麻煩的計算中。因此，得到天數(shù)、月數(shù)、天數(shù)、時數(shù)、分鐘數(shù)和秒數(shù)就成為用substr函數(shù)摘取出數(shù)字的事情了。
　　
　　系統(tǒng)日期和時間
　　
　　為了得到系統(tǒng)時間，返回成date數(shù)據(jù)類型。你可以使用sysdate函數(shù)。
　　
　　SQL> SELECT SYSDATE FROM DUAL;
　　
　　為了得到系統(tǒng)時間，返回成timestamp數(shù)據(jù)類型。你可以使用systimpstamp函數(shù)。
　　
　　SQL> SELECT SYSTIMESTAMP FROM DUAL;
　　
　　你可以設(shè)置初始化參數(shù)FIXED_DATE指定sysdate函數(shù)返回一個固定值。這用在測試日期和時間敏感的代碼。注意，這個參數(shù)對于systimestamp函數(shù)無效。
　　
　　SQL> ALTER SYSTEM SET fixed_date = '2003-01-01-10:00:00';
　　
　　System altered.
　　
　　SQL> select sysdate from dual;
　　
　　SYSDATE
　　
　　---------
　　
　　01-JAN-03
　　
　　SQL> select systimestamp from dual;
　　
　　SYSTIMESTAMP
　　
　　---------------------------------------------------------
　　
　　09-JUL-03 11.05.02.519000 AM -06:00
　　
　　當使用date和timestamp類型的時候，選擇是很清楚的。你可以隨意處置date和timestamp類型。當你試圖轉(zhuǎn)換到更強大的timestamp的時候，需要注意，它們既有類似的地方，更有不同的地方，而足以造成破壞。兩者在簡潔和間隔尺寸方面各有優(yōu)勢，請合理地選擇。


DateDiff( month, waterpay.CopyDate, GetDate() ) = 1 )

DateDiff( day, WaterPay.CopyDate, GetDate() ) = 1 )
這兩個句子的意思分別是：求以month或者以day的方式返回當前日期（getdate）減數(shù)據(jù)庫中的一個日期字段（waterpay.CopyDate）的值等于1的記錄
請問這兩句在sql server中的函數(shù)如何在oracle中改寫?

問題點數(shù)：20、回復(fù)次數(shù)：2
Top


1 樓hevin（沒有什么是不可能的）回復(fù)于 2005-09-25 18:12:10 得分 0 第一句：
Months_Between( to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd'),
to_date(to_char(waterpay.CopyDate,'yyyy-mm-dd'),'yyyy-mm-dd')
) = 1
Top

2 樓hevin（沒有什么是不可能的）回復(fù)于 2005-09-25 18:18:06 得分 20第二句：
to_char(sysdate - 1,'yyyy-mm-dd') = to_char(waterpay.CopyDate,'yyyy-mm-dd')