Oracle中TO_DATE格式介绍

TO_DATE格式(以时间:2007-11-0213:45:25为例) 

Year: 
yytwodigits两位年显示值:07 
yyythreedigits三位年显示值:007 
yyyyfourdigits四位年显示值:2007 

Month: 
mmnumber两位月显示值:11 
monabbreviated字符集表示显示值:11月,若是英文版,显示nov 
monthspelledout字符集表示显示值:11月,若是英文版,显示november 

Day: 
ddnumber当月第几天显示值:02 
dddnumber当年第几天显示值:02 
dyabbreviated当周第几天简写显示值:星期五,若是英文版,显示fri 
dayspelledout当周第几天全写显示值:星期五,若是英文版,显示friday 
ddspthspelledout,ordinaltwelfth 

Hour: 
hhtwodigits12小时进制显示值:01 
hh24twodigits24小时进制显示值:13 

Minute: 
mitwodigits60进制显示值:45 

Second: 
sstwodigits60进制显示值:25 

其它 
Qdigit季度显示值:4 
WWdigit当年第几周显示值:44 
Wdigit当月第几周显示值:1 

24小时格式下时间范围为：0:00:00-23:59:59.... 
12小时格式下时间范围为：1:00:00-12:59:59.... 

1.日期和字符转换函数用法（to_date,to_char） 

selectto_char(sysdate,'yyyy-mm-ddhh24:mi:ss')asnowTimefromdual;//日期转化为字符串 
selectto_char(sysdate,'yyyy')asnowYearfromdual;//获取时间的年 
selectto_char(sysdate,'mm')asnowMonthfromdual;//获取时间的月 
selectto_char(sysdate,'dd')asnowDayfromdual;//获取时间的日 
selectto_char(sysdate,'hh24')asnowHourfromdual;//获取时间的时 
selectto_char(sysdate,'mi')asnowMinutefromdual;//获取时间的分 
selectto_char(sysdate,'ss')asnowSecondfromdual;//获取时间的秒 
selectto_date('2004-05-0713:23:44','yyyy-mm-ddhh24:mi:ss')fromdual// 
2. 
selectto_char(to_date(222,'J'),'Jsp')fromdual 

显示TwoHundredTwenty-Two 
3.求某天是星期几 
selectto_char(to_date('2002-08-26','yyyy-mm-dd'),'day')fromdual; 
星期一 
selectto_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE=American')fromdual; 
monday 
设置日期语言 
ALTERSESSIONSETNLS_DATE_LANGUAGE='AMERICAN'; 
也可以这样 
TO_DATE('2002-08-26','YYYY-mm-dd','NLS_DATE_LANGUAGE=American') 

4.两个日期间的天数 
selectfloor(sysdate-to_date('20020405','yyyymmdd'))fromdual; 

5.时间为null的用法 
selectid,active_datefromtable1 
UNION 
select1,TO_DATE(null)fromdual; 

注意要用TO_DATE(null) 
6.月份差 
a_datebetweento_date('20011201','yyyymmdd')andto_date('20011231','yyyymmdd') 
那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。 
所以，当时间需要精确的时候，觉得to_char还是必要的 

7.日期格式冲突问题 
输入的格式要看你安装的ORACLE字符集的类型,比如:US7ASCII,date格式的类型就是:'01-Jan-01' 
altersystemsetNLS_DATE_LANGUAGE=American 
altersessionsetNLS_DATE_LANGUAGE=American 
或者在to_date中写 
selectto_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE=American')fromdual; 
注意我这只是举了NLS_DATE_LANGUAGE，当然还有很多， 
可查看 
select*fromnls_session_parameters 
select*fromV$NLS_PARAMETERS 
8. 
selectcount(*) 
from(selectrownum-1rnum 
fromall_objects 
whererownum<=to_date('2002-02-28','yyyy-mm-dd')-to_date('2002- 
02-01','yyyy-mm-dd')+1 
) 
whereto_char(to_date('2002-02-01','yyyy-mm-dd')+rnum-1,'D') 
notin('1','7') 

查找2002-02-28至2002-02-01间除星期一和七的天数 
在前后分别调用DBMS_UTILITY.GET_TIME,让后将结果相减(得到的是1/100秒,而不是毫秒). 

9.查找月份 
selectmonths_between(to_date('01-31-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY'))"MONTHS"FROMDUAL; 
1 
selectmonths_between(to_date('02-01-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY'))"MONTHS"FROMDUAL; 
1.03225806451613 

10.Next_day的用法 
Next_day(date,day) 

Monday-Sunday,forformatcodeDAY 
Mon-Sun,forformatcodeDY 
1-7,forformatcodeD 
11 
selectto_char(sysdate,'hh:mi:ss')TIMEfromall_objects 
注意：第一条记录的TIME与最后一行是一样的 
可以建立一个函数来处理这个问题 
createorreplacefunctionsys_datereturndateis 
begin 
returnsysdate; 
end; 

selectto_char(sys_date,'hh:mi:ss')fromall_objects; 

12.获得小时数 
extract()找出日期或间隔值的字段值 
SELECTEXTRACT(HOURFROMTIMESTAMP'2001-02-162:38:40')fromoffer 
SQL>selectsysdate,to_char(sysdate,'hh')fromdual; 

SYSDATETO_CHAR(SYSDATE,'HH') 
----------------------------------------- 
2003-10-1319:35:2107 

SQL>selectsysdate,to_char(sysdate,'hh24')fromdual; 

SYSDATETO_CHAR(SYSDATE,'HH24') 
------------------------------------------- 
2003-10-1319:35:2119 

13.年月日的处理 
selectolder_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( 
selecthiredateolder_date,add_months(hiredate,rownum)+rownumnewer_date 
fromemp 
) 
) 
14.处理月份天数不定的办法 
selectto_char(add_months(last_day(sysdate)+1,-2),'yyyymmdd'),last_day(sysdate)fromdual 

16.找出今年的天数 
selectadd_months(trunc(sysdate,'year'),12)-trunc(sysdate,'year')fromdual 
闰年的处理方法 
to_char(last_day(to_date('02'||:year,'mmyyyy')),'dd') 
如果是28就不是闰年 

17.yyyy与rrrr的区别 
'YYYY99TO_C 
----------- 
yyyy990099 
rrrr991999 
yyyy010001 
rrrr012001 

18.不同时区的处理 
selectto_char(NEW_TIME(sysdate,'GMT','EST'),'dd/mm/yyyyhh:mi:ss'),sysdate 
fromdual; 

19.5秒钟一个间隔 
SelectTO_DATE(FLOOR(TO_CHAR(sysdate,'SSSSS')/300)*300,'SSSSS'),TO_CHAR(sysdate,'SSSSS') 
fromdual 
2002-11-19:55:0035786 
SSSSS表示5位秒数 

20.一年的第几天 
selectTO_CHAR(SYSDATE,'DDD'),sysdatefromdual 
3102002-11-610: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 
fromdual 
) 

select*fromtabname 
orderbydecode(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)是从当前开始下一个星期五。后面的数字是从星期日开始算起。 
1234567 
日一二三四五六 

--------------------------------------------------------------- 

select(sysdate-to_date('2003-12-0312:55:45','yyyy-mm-ddhh24:mi:ss'))*24*60*60fromddual 
日期返回的是天然后转换为ss 

24,round[舍入到最接近的日期](day:舍入到最接近的星期日) 
selectsysdateS1, 
round(sysdate)S2, 
round(sysdate,'year')YEAR, 
round(sysdate,'month')MONTH, 
round(sysdate,'day')DAYfromdual 

25,trunc[截断到最接近的日期,单位为天],返回的是日期类型 
selectsysdateS1, 
trunc(sysdate)S2,//返回当前日期,无时分秒 
trunc(sysdate,'year')YEAR,//返回当前年的1月1日,无时分秒 
trunc(sysdate,'month')MONTH,//返回当前月的1日,无时分秒 
trunc(sysdate,'day')DAY//返回当前星期的星期天,无时分秒 
fromdual 

26,返回日期列表中最晚日期 
selectgreatest('01-1月-04','04-1月-04','10-2月-04')fromdual

27.计算时间差 
注:oracle时间差是以天数为单位,所以换算成年月,日 

selectfloor(to_number(sysdate-to_date('2007-11-0215:55:03','yyyy-mm-ddhh24:mi:ss'))/365)asspanYearsfromdual//时间差-年 
selectceil(moths_between(sysdate-to_date('2007-11-0215:55:03','yyyy-mm-ddhh24:mi:ss')))asspanMonthsfromdual//时间差-月 
selectfloor(to_number(sysdate-to_date('2007-11-0215:55:03','yyyy-mm-ddhh24:mi:ss')))asspanDaysfromdual//时间差-天 
selectfloor(to_number(sysdate-to_date('2007-11-0215:55:03','yyyy-mm-ddhh24:mi:ss'))*24)asspanHoursfromdual//时间差-时 
selectfloor(to_number(sysdate-to_date('2007-11-0215:55:03','yyyy-mm-ddhh24:mi:ss'))*24*60)asspanMinutesfromdual//时间差-分 
selectfloor(to_number(sysdate-to_date('2007-11-0215:55:03','yyyy-mm-ddhh24:mi:ss'))*24*60*60)asspanSecondsfromdual//时间差-秒

28.更新时间 
注:oracle时间加减是以天数为单位,设改变量为n,所以换算成年月,日 
selectto_char(sysdate,'yyyy-mm-ddhh24:mi:ss'),to_char(sysdate+n*365,'yyyy-mm-ddhh24:mi:ss')asnewTimefromdual//改变时间-年 
selectto_char(sysdate,'yyyy-mm-ddhh24:mi:ss'),add_months(sysdate,n)asnewTimefromdual//改变时间-月 
selectto_char(sysdate,'yyyy-mm-ddhh24:mi:ss'),to_char(sysdate+n,'yyyy-mm-ddhh24:mi:ss')asnewTimefromdual//改变时间-日 
selectto_char(sysdate,'yyyy-mm-ddhh24:mi:ss'),to_char(sysdate+n/24,'yyyy-mm-ddhh24:mi:ss')asnewTimefromdual//改变时间-时 
selectto_char(sysdate,'yyyy-mm-ddhh24:mi:ss'),to_char(sysdate+n/24/60,'yyyy-mm-ddhh24:mi:ss')asnewTimefromdual//改变时间-分 
selectto_char(sysdate,'yyyy-mm-ddhh24:mi:ss'),to_char(sysdate+n/24/60/60,'yyyy-mm-ddhh24:mi:ss')asnewTimefromdual//改变时间-秒

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