oracle中to_date详细用法示例(oracle日期格式转换)
TO_DATE格式(以时间:2007-11-0213:45:25为例)
1.日期和字符转换函数用法(to_date,to_char)
selectto_char(sysdate,"yyyy-mm-ddhh24:mi:ss")asnowTimefromdual; //日期转化为字符串
selectto_char(sysdate,"yyyy") asnowYear fromdual; //获取时间的年
selectto_char(sysdate,"mm") asnowMonth fromdual; //获取时间的月
selectto_char(sysdate,"dd") asnowDay fromdual; //获取时间的日
selectto_char(sysdate,"hh24") asnowHour fromdual; //获取时间的时
selectto_char(sysdate,"mi") asnowMinutefromdual; //获取时间的分
selectto_char(sysdate,"ss") asnowSecondfromdual; //获取时间的秒
2.字符串和时间互转
selectto_date("2004-05-0713:23:44","yyyy-mm-ddhh24:mi:ss")fromdual
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.获得小时数
//extract()找出日期或间隔值的字段值
SELECTEXTRACT(HOURFROMTIMESTAMP"2001-02-162:38:40")fromoffer
selectsysdate,to_char(sysdate,"hh")fromdual;
SYSDATE TO_CHAR(SYSDATE,"HH")
-----------------------------------------
2003-10-1319:35:21 07
selectsysdate,to_char(sysdate,"hh24")fromdual;
SYSDATE TO_CHAR(SYSDATE,"HH24")
-------------------------------------------
2003-10-1319:35:21 19
12.年月日的处理
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
hiredateolder_date,
ADD_MONTHS(hiredate,ROWNUM)+ROWNUMnewer_date
FROM
emp
)
)
13.处理月份天数不定的办法
selectto_char(add_months(last_day(sysdate)+1,-2),"yyyymmdd"),last_day(sysdate)fromdual
14.找出今年的天数
selectadd_months(trunc(sysdate,"year"),12)-trunc(sysdate,"year")fromdual
//闰年的处理方法
to_char(last_day(to_date("02" ||:year,"mmyyyy")),"dd")
//如果是28就不是闰年
15.yyyy与rrrr的区别
16.不同时区的处理 17.5秒钟一个间隔 18.一年的第几天 19.计算小时,分,秒,毫秒 20.next_day函数 21,round[舍入到最接近的日期](day:舍入到最接近的星期日) 22,trunc[截断到最接近的日期,单位为天],返回的是日期类型 23,返回日期列表中最晚日期 24.计算时间差 25.更新时间 26.查找月的第一天,最后一天
YYYY99 TO_C
-----------
yyyy990099
rrrr991999
yyyy010001
rrrr012001
selectto_char(NEW_TIME(sysdate,"GMT","EST"),"dd/mm/yyyyhh:mi:ss"),
sysdate fromdual;
SelectTO_DATE(FLOOR(TO_CHAR(sysdate,"SSSSS")/300)*300,"SSSSS"),
TO_CHAR(sysdate,"SSSSS") fromdual
//2002-11-19:55:0035786
//SSSSS表示5位秒数
selectTO_CHAR(SYSDATE,"DDD"),sysdatefromdual
//310 2002-11-610:03:51
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
ORDERBY
DECODE(MODE,"FIFO",1,-1)*TO_CHAR(rq,"yyyymmddhh24miss")
// floor((date2-date1)/365)作为年
// floor((date2-date1,365)/30)作为月
// d(mod(date2-date1,365),30)作为日.
//返回下个星期的日期,day为1-7或星期日-星期六,1表示星期日
next_day(sysdate,6)是从当前开始下一个星期五。后面的数字是从星期日开始算起。
//1 2 3 4 5 6 7
//日一二三四五六
select(sysdate-to_date("2003-12-0312:55:45","yyyy-mm-ddhh24:mi:ss"))*24*60*60fromdual
//日期返回的是天然后转换为ss
selectsysdateS1,
round(sysdate)S2,
round(sysdate,"year")YEAR,
round(sysdate,"month")MONTH,
round(sysdate,"day")DAYfromdual
selectsysdateS1,
trunc(sysdate)S2, //返回当前日期,无时分秒
trunc(sysdate,"year")YEAR, //返回当前年的1月1日,无时分秒
trunc(sysdate,"month")MONTH, //返回当前月的1日,无时分秒
trunc(sysdate,"day")DAY //返回当前星期的星期天,无时分秒
fromdual
selectgreatest("01-1月-04","04-1月-04","10-2月-04")fromdual
注: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//时间差-秒
//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 //改变时间-秒
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;
相关文章