oracle中to_date详细用法示例(oracle日期格式转换)

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

到公司发现有一个报表没有收到,查看后发现原来是Oracle数据源异常。

  1. 日期和字符转换函数用法
SELECT   to_char(SYSDATE - interval '1' month, 'yyyyMMdd') AS fromdata, to_char(SYSDATE - interval '1' day, 'yyyyMMdd') 
                AS enddata
FROM      DUAL;

复制代码 代码如下:select
to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) as nowTime from dual;
//日期转化为字符串 select to_char(sysdate,’yyyy’) as nowYear from dual;
//获取时间的年 select to_char(sysdate,’mm’) as nowMonth from dual;
//获取时间的月 select to_char(sysdate,’dd’) as nowDay from dual;
//获取时间的日 select to_char(sysdate,’hh24′) as nowHour from dual;
//获取时间的时 select to_char(sysdate,’mi’) as nowMinute from dual;
//获取时间的分 select to_char(sysdate,’ss’) as nowSecond from dual;
//获取时间的秒

上面这个sql求上个月今天和昨天。一般情况下不会报错。

  1. 字符串和时间互转复制代码
    代码如下:select to_date(‘2004-05-07 13:23:44′,’yyyy-mm-dd hh24:mi:ss’)
    from dualselect to_char( to_date(222,’J’),’Jsp’) from dual //显示Two
    Hundred Twenty-Two

但是今天是5月31号,上个月今天就是4月31号。Oracle找不到,只能报错了。

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’)

依此类推,看来3月29、30也有很大几率报错。

  1. 两个日期间的天数复制代码
    代码如下:select floor(sysdate – to_date(‘20020405′,’yyyymmdd’)) from
    dual;

  2. 时间为null的用法复制代码
    代码如下:select id, active_date from table1 UNION select 1,
    TO_DATE(null) from dual; //注意要用TO_DATE(null)

搜索了下网上的答案,使用add_months函数修改之。

6.月份差复制代码 代码如下:a_date between
to_date(‘20011201′,’yyyymmdd’) and to_date(‘20011231′,’yyyymmdd’)
//那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。
//所以,当时间需要精确的时候,觉得to_char还是必要的

SELECT   to_char(add_months(sysdate,-1), 'yyyyMMdd') AS fromdata, to_char(SYSDATE - interval '1' day, 'yyyyMMdd') 
                AS enddata
FROM      DUAL;
  1. 日期格式冲突问题输入的格式要看你安装的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

再整理下时间相关的Oracle函数

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间除星期一和七的天数
//在前后分别调用DBMS_UTILITY.GET_TIME, 让后将结果相减(得到的是1/100秒,
而不是毫秒)

Oracle增加、减少时间段

  1. 查找月份复制代码 代码如下: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

  2. Next_day的用法复制代码
    代码如下:Next_day(date, day) Monday-Sunday, for format code DAY
    Mon-Sun, for format code DY 1-7, for format code D

sysdate+1 加一天
sysdate+1/24 加1小时
sysdate+1/(24*60) 加1分钟
sysdate+1/(24*60*60) 加1秒钟
类推至毫秒0.001秒

11.获得小时数复制代码
代码如下://extract()找出日期或间隔值的字段值SELECT EXTRACT(HOUR FROM
TIMESTAMP ‘2001-02-16 2:38:40′) from offer select sysdate
,to_char(sysdate,’hh’) from dual; SYSDATE TO_CHAR(SYSDATE,’HH’)
——————– ——————— 2003-10-13 19:35:21 07 select
sysdate ,to_char(sysdate,’hh24′) from dual; SYSDATE
TO_CHAR(SYSDATE,’HH24′) ——————– ———————–
2003-10-13 19:35:21 19

加法
select sysdate,add_months(sysdate,12) from dual; –加1年
select sysdate,add_months(sysdate,1) from dual; –加1月
select sysdate,to_char(sysdate+7,’yyyy-mm-dd HH24:MI:SS’) from dual;
–加1星期
select sysdate,to_char(sysdate+1,’yyyy-mm-dd HH24:MI:SS’) from dual;
–加1天
select sysdate,to_char(sysdate+1/24,’yyyy-mm-dd HH24:MI:SS’) from dual;
–加1小时
select sysdate,to_char(sysdate+1/24/60,’yyyy-mm-dd HH24:MI:SS’) from
dual; –加1分钟
select sysdate,to_char(sysdate+1/24/60/60,’yyyy-mm-dd HH24:MI:SS’) from
dual; –加1秒
减法
select sysdate,add_months(sysdate,-12) from dual; –减1年
select sysdate,add_months(sysdate,-1) from dual; –减1月
select sysdate,to_char(sysdate-7,’yyyy-mm-dd HH24:MI:SS’) from dual;
–减1星期
select sysdate,to_char(sysdate-1,’yyyy-mm-dd HH24:MI:SS’) from dual;
–减1天
select sysdate,to_char(sysdate-1/24,’yyyy-mm-dd HH24:MI:SS’) from dual;
–减1小时
select sysdate,to_char(sysdate-1/24/60,’yyyy-mm-dd HH24:MI:SS’) from
dual; –减1分钟
select sysdate,to_char(sysdate-1/24/60/60,’yyyy-mm-dd HH24:MI:SS’) from
dual; –减1秒

12.年月日的处理复制代码 代码如下:SELECT
older_date, newer_date, years, months, ABS ( TRUNC ( newer_date –
ADD_MONTHS (older_date, years * 12 + months) ) ) daysFROM ( 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 ) )

 

13.处理月份天数不定的办法复制代码
代码如下:select to_char(add_months(last_day(sysdate) +1, -2),
‘yyyymmdd’),last_day(sysdate) from dual

时间函数用法:

14.找出今年的天数复制代码 代码如下:select
add_months(trunc(sysdate,’year’), 12) – trunc(sysdate,’year’) from dual
//闰年的处理方法 to_char( last_day( to_date(’02’ | | :year,’mmyyyy’)
), ‘dd’ ) //如果是28就不是闰年

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

15.yyyy与rrrr的区别

Year:
yy two digits 两位年 显示值:07
yyy three digits 三位年 显示值:007
yyyy four digits 四位年 显示值:2007

复制代码 代码如下:YYYY99 TO_C ——-
—- yyyy 99 0099 rrrr 99 1999 yyyy 01 0001 rrrr 01 2001

Month:
mm number 两位月 显示值:11
mon abbreviated 字符集表示 显示值:11月,若是英文版,显示nov
month spelled out 字符集表示 显示值:11月,若是英文版,显示november

16.不同时区的处理复制代码 代码如下:select
to_char( NEW_TIME( sysdate, ‘GMT’,’EST’), ‘dd/mm/yyyy hh:mi:ss’)
,sysdate from dual;

Day:
dd number 当月第几天 显示值:02
ddd number 当年第几天 显示值:02
dy abbreviated 当周第几天简写 显示值:星期五,若是英文版,显示fri
day spelled out 当周第几天全写 显示值:星期五,若是英文版,显示friday
ddspth spelled out, ordinal twelfth

  1. 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位秒数

Hour:
hh two digits 12小时进制 显示值:01
hh24 two digits 24小时进制 显示值:13

18.一年的第几天复制代码 代码如下:select
TO_CHAR(SYSDATE,’DDD’),sysdate from dual //310 2002-11-6 10:03:51

Minute:
mi two digits 60进制 显示值:45

19.计算小时,分,秒,毫秒复制代码
代码如下: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) ) mSecondsFROM ( 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) 作为月 // d(mod(date2-date1, 365),
30)作为日.

Second:
ss two digits 60进制 显示值:25

20.next_day函数复制代码
代码如下://返回下个星期的日期,day为1-7或星期日-星期六,1表示星期日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

其它
Q digit 季度 显示值:4
WW digit 当年第几周 显示值:44
W digit 当月第几周 显示值:1

21,round[舍入到最接近的日期](day:舍入到最接近的星期日)复制代码 代码如下:select sysdate
S1,round(sysdate) S2 ,round(sysdate,’year’) YEAR,round(sysdate,’month’)
MONTH ,round(sysdate,’day’) DAY from dual

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

22,trunc[截断到最接近的日期,单位为天] ,返回的是日期类型

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

复制代码 代码如下:select sysdate S1,
trunc(sysdate) S2, //返回当前日期,无时分秒 trunc(sysdate,’year’) YEAR,
//返回当前年的1月1日,无时分秒 trunc(sysdate,’month’) MONTH ,
//返回当前月的1日,无时分秒 trunc(sysdate,’day’) DAY
//返回当前星期的星期天,无时分秒from dual

select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) as nowTime from dual;
//日期转化为字符串
select to_char(sysdate,’yyyy’) as nowYear from dual; //获取时间的年
select to_char(sysdate,’mm’) as nowMonth from dual; //获取时间的月
select to_char(sysdate,’dd’) as nowDay from dual; //获取时间的日
select to_char(sysdate,’hh24′) as nowHour from dual; //获取时间的时
select to_char(sysdate,’mi’) as nowMinute from dual; //获取时间的分
select to_char(sysdate,’ss’) as nowSecond from dual; //获取时间的秒

23,返回日期列表中最晚日期

select to_date(‘2004-05-07 13:23:44′,’yyyy-mm-dd hh24:mi:ss’) from
dual//

复制代码 代码如下:select
greatest(’01-1月-04′,’04-1月-04′,’10-2月-04′) from dual

  1. select to_char( to_date(222,’J’),’Jsp’) from dual

24.计算时间差复制代码
代码如下:注:oracle时间差是以天数为单位,所以换算成年月,日select
floor(to_number(sysdate-to_date(‘2007-11-02 15:55:03′,’yyyy-mm-dd
hh24:mi:ss’))/365) as spanYears from dual //时间差-年select
ceil(moths_between(sysdate-to_date(‘2007-11-02 15:55:03′,’yyyy-mm-dd
hh24:mi:ss’))) as spanMonths from dual //时间差-月select
floor(to_number(sysdate-to_date(‘2007-11-02 15:55:03′,’yyyy-mm-dd
hh24:mi:ss’))) as spanDays from dual //时间差-天select
floor(to_number(sysdate-to_date(‘2007-11-02 15:55:03′,’yyyy-mm-dd
hh24:mi:ss’))*24) as spanHours from dual //时间差-时select
floor(to_number(sysdate-to_date(‘2007-11-02 15:55:03′,’yyyy-mm-dd
hh24:mi:ss’))*24*60) as spanMinutes from dual //时间差-分select
floor(to_number(sysdate-to_date(‘2007-11-02 15:55:03′,’yyyy-mm-dd
hh24:mi:ss’))*24*60*60) as spanSeconds from dual //时间差-秒

显示Two Hundred Twenty-Two

25.更新时间复制代码
代码如下://oracle时间加减是以天数为单位,设改变量为n,所以换算成年月,日select
to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’),
to_char(sysdate+n*365,’yyyy-mm-dd hh24:mi:ss’) as newTime from dual
//改变时间-年select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’),
add_months(sysdate,n) as newTime from dual //改变时间-月select
to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’),
to_char(sysdate+n,’yyyy-mm-dd hh24:mi:ss’) as newTime from dual
//改变时间-日select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’),
to_char(sysdate+n/24,’yyyy-mm-dd hh24:mi:ss’) as newTime from dual
//改变时间-时select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’),
to_char(sysdate+n/24/60,’yyyy-mm-dd hh24:mi:ss’) as newTime from dual
//改变时间-分select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’),
to_char(sysdate+n/24/60/60,’yyyy-mm-dd hh24:mi:ss’) as newTime from
dual //改变时间-秒

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’)

26.查找月的第一天,最后一天复制代码
代码如下: SELECT Trunc(Trunc(SYSDATE, ‘MONTH’) – 1, ‘MONTH’)
First_Day_Last_Month, Trunc(SYSDATE, ‘MONTH’) – 1 / 86400
Last_Day_Last_Month, Trunc(SYSDATE, ‘MONTH’) First_Day_Cur_Month,
LAST_DAY(Trunc(SYSDATE, ‘MONTH’)) + 1 – 1 / 86400
Last_Day_Cur_MonthFROM dual;

  1. 两个日期间的天数
    select floor(sysdate – to_date(‘20020405′,’yyyymmdd’)) from dual;

  2. 时间为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还是必要的

  1. 日期格式冲突问题
    输入的格式要看你安装的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

  2. 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间除星期一和七的天数
在前后分别调用DBMS_UTILITY.GET_TIME, 让后将结果相减(得到的是1/100秒,
而不是毫秒).

  1. 查找月份
    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

  2. 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;