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小時(shí)格式下時(shí)間范圍為：?0:00:00?-?23:59:59....?
12小時(shí)格式下時(shí)間范圍為：?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è)置日期語(yǔ)言?
ALTER?SESSION?SET?NLS_DATE_LANGUAGE='AMERICAN';?
也可以這樣?
TO_DATE?('2002-08-26',?'YYYY-mm-dd',?'NLS_DATE_LANGUAGE?=?American')?

4.?
兩個(gè)日期間的天數(shù)?
select?floor(sysdate?-?to_date('20020405','yyyymmdd'))?from?dual;?

5.?時(shí)間為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號(hào)中午12點(diǎn)之后和12月1號(hào)的12點(diǎn)之前是不包含在這個(gè)范圍之內(nèi)的。?
所以，當(dāng)時(shí)間需要精確的時(shí)候，覺(jué)得to_char還是必要的?
7.?日期格式?jīng)_突問(wèn)題?
輸入的格式要看你安裝的ORACLE字符集的類型,?比如:?US7ASCII,?date格式的類型就是:?'01-Jan-01'?
alter?system?set?NLS_DATE_LANGUAGE?=?American?
alter?session?set?NLS_DATE_LANGUAGE?=?American?
或者在to_date中寫(xiě)?
select?to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE?=?American')?from?dual;?
注意我這只是舉了NLS_DATE_LANGUAGE，當(dāng)然還有很多，?
可查看?
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?與最后一行是一樣的?
可以建立一個(gè)函數(shù)來(lái)處理這個(gè)問(wèn)題?
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í)數(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.不同時(shí)區(qū)的處理?
select?to_char(?NEW_TIME(?sysdate,?'GMT','EST'),?'dd/mm/yyyy?hh:mi:ss')?,sysdate?
from?dual;?

19.?
5秒鐘一個(gè)間隔?
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.計(jì)算小時(shí),分,秒,毫秒?
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)是從當(dāng)前開(kāi)始下一個(gè)星期五。后面的數(shù)字是從星期日開(kāi)始算起。