Oracle 中的 TO_DATE 和 TO_CHAR 函数

oracle 中 TO_DATE 函数的时间格式，以 2008-09-10 234556 为例
格式 说明 显示值备注
Year(年)
yy two digits(两位年) 08
yyy
three digits(三位年) 008
yyyy four digits(四位年) 2008
Month(月)
mm number(两位月) 09
mon abbreviated(字符集表示) 9月 若是英文版, 则显示 sep
month spelled out(字符集表示) 9月 若是英文版, 则显示 september
Day(日)
dd number(当月第几天) 10
ddd number(当年第几天) 254
dy abbreviated(当周第几天简写) 星期三 若是英文版, 则显示 wed
day spelled out(当周第几天全写) 星期三 若是英文版, 则显示 wednesday
ddspth spelled out, ordinal twelfth tenth
Hour(时)
hh two digits(12小时进制) 11
hh24 two digits(24小时进制) 23
Minute(分)
mi two digits(60进制) 45
Second(秒)
ss two digits(60进制) 56
其他
Q digit(季度) 3
WW digit(当年第几周) 37
W digit(当月第几周) 2
说明：
12小时格式下时间范围为： 10000 - 125959(12 小时制下的 125959 对应 24 小时制下的 005959)
24小时格式下时间范围为： 00000 - 235959
1. 日期和字符转换函数用法（to_date,to_char）
select to_char(sysdate,'yyyy-mm-dd hh24miss') 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; 获取时间的秒
select to_date('2004-05-07 132344','yyyy-mm-dd hh24miss') from dual
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
设置日期语言
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
也可以这样
TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')
4. 两个日期间的天数
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点之前是不包含在这个范围之内的。
所以，当时间需要精确的时候，觉得to_char还是必要的
7. 日期格式冲突问题
输入的格式要看你安装的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间除星期一和七的天数
在前后分别调用DBMS_UTILITY.GET_TIME, 让后将结果相减(得到的是1100秒, 而不是毫秒).
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,'hhmiss') TIME from all_objects
注意：第一条记录的TIME 与最后一行是一样的
可以建立一个函数来处理这个问题
create or replace function sys_date return date is
begin
return sysdate;
end;
select to_char(sys_date,'hhmiss') from all_objects;
12.获得小时数
extract()找出日期或间隔值的字段值
SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 23840') from offer
SQL> select sysdate ,to_char(sysdate,'hh') from dual;
SYSDATE TO_CHAR(SYSDATE,'HH')
-------------------- ---------------------
2003-10-13 193521 07
SQL> select sysdate ,to_char(sysdate,'hh24') from dual;
SYSDATE TO_CHAR(SYSDATE,'HH24')
-------------------- -----------------------
2003-10-13 193521 19
13.年月日的处理
select older_date,
newer_date,
years,
months,
abs(
trunc(
newer_date-
add_months( older_date,years12+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.处理月份天数不定的办法
select to_char(add_months(last_day(sysdate) +1, -2), 'yyyymmdd'),last_day(sysdate) from dual
16.找出今年的天数
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的区别
'YYYY99 TO_C
------- ----
yyyy 99 0099
rrrr 99 1999
yyyy 01 0001
rrrr 01 2001
18.不同时区的处理
select to_char( NEW_TIME( sysdate, 'GMT','EST'), 'ddmmyyyy hhmiss') ,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 95500 35786
SSSSS表示5位秒数
20.一年的第几天
select TO_CHAR(SYSDATE,'DDD'),sysdate from dual
310 2002-11-6 100351
21.计算小时,分,秒,毫秒
select
Days,
A,
TRUNC(A24) Hours,
TRUNC(A2460 - 60TRUNC(A24)) Minutes,
TRUNC(A246060 - 60TRUNC(A2460)) Seconds,
TRUNC(A246060100 - 100TRUNC(A246060)) 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) 作为月
d(mod(date2-date1, 365), 30)作为日.
23.next_day函数 返回下个星期的日期,day为1-7或星期日-星期六,1表示星期日
next_day(sysdate,6)是从当前开始下一个星期五。后面的数字是从星期日开始算起。
1 2 3 4 5 6 7
日 一 二 三 四 五 六
---------------------------------------------------------------
select (sysdate-to_date('2003-12-03 125545','yyyy-mm-dd hh24miss'))246060 from ddual
日期 返回的是天 然后 转换为ss
24,round[舍入到最接近的日期](day舍入到最接近的星期日)
select sysdate S1,
round(sysdate) S2 ,
round(sysdate,'year') YEAR,
round(sysdate,'month') MONTH ,
round(sysdate,'day') DAY from dual
25,trunc[截断到最接近的日期,单位为天] ,返回的是日期类型
select sysdate S1,
trunc(sysdate) S2, 返回当前日期,无时分秒
trunc(sysdate,'year') YEAR, 返回当前年的1月1日,无时分秒
trunc(sysdate,'month') MONTH , 返回当前月的1日,无时分秒
trunc(sysdate,'day') DAY 返回当前星期的星期天,无时分秒
from dual
26,返回日期列表中最晚日期
select greatest('01-1月-04','04-1月-04','10-2月-04') from dual
27.计算时间差
注oracle时间差是以天数为单位,所以换算成年月,日
select floor(to_number(sysdate-to_date('2007-11-02 155503','yyyy-mm-dd hh24miss'))365) as spanYears from dual 时间差-年
select ceil(moths_between(sysdate-to_date('2007-11-02 155503','yyyy-mm-dd hh24miss'))) as spanMonths from dual 时间差-月
select floor(to_number(sysdate-to_date('2007-11-02 155503','yyyy-mm-dd hh24miss'))) as spanDays from dual 时间差-天
select floor(to_number(sysdate-to_date('2007-11-02 155503','yyyy-mm-dd hh24miss'))24) as spanHours from dual 时间差-时
select floor(to_number(sysdate-to_date('2007-11-02 155503','yyyy-mm-dd hh24miss'))2460) as spanMinutes from dual 时间差-分
select floor(to_number(sysdate-to_date('2007-11-02 155503','yyyy-mm-dd hh24miss'))246060) as spanSeconds from dual 时间差-秒
28.更新时间
注oracle时间加减是以天数为单位,设改变量为n,所以换算成年月,日
select to_char(sysdate,'yyyy-mm-dd hh24miss'),to_char(sysdate+n365,'yyyy-mm-dd hh24miss') as newTime from dual 改变时间-年
select to_char(sysdate,'yyyy-mm-dd hh24miss'),add_months(sysdate,n) as newTime from dual 改变时间-月
select to_char(sysdate,'yyyy-mm-dd hh24miss'),to_char(sysdate+n,'yyyy-mm-dd hh24miss') as newTime from dual 改变时间-日
select to_char(sysdate,'yyyy-mm-dd hh24miss'),to_char(sysdate+n24,'yyyy-mm-dd hh24miss') as newTime from dual 改变时间-时
select to_char(sysdate,'yyyy-mm-dd hh24miss'),to_char(sysdate+n2460,'yyyy-mm-dd hh24miss') as newTime from dual 改变时间-分
select to_char(sysdate,'yyyy-mm-dd hh24miss'),to_char(sysdate+n246060,'yyyy-mm-dd hh24miss') as newTime from dual 改变时间-秒
29.查找月的第一天,最后一天
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_Month
FROM dual;
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
TO_CHAR 函数说明：
SYSDATE 2009-6-16 152510
TRUNC(SYSDATE) 2009-6-16
TO_CHAR(SYSDATE,'YYYYMMDD') 20090616 到日
TO_CHAR(SYSDATE,'YYYYMMDD HH24MISS') 20090616 152510 到秒
TO_CHAR(SYSTIMESTAMP,'YYYYMMDD HH24MISS.FF3') 20090616 152510.848 到毫秒
TO_CHAR(SYSDATE,'AD') 公元
TO_CHAR(SYSDATE,'AM') 下午
TO_CHAR(SYSDATE,'BC') 公元
TO_CHAR(SYSDATE,'CC') 21
TO_CHAR(SYSDATE,'D') 3 老外的星期几
TO_CHAR(SYSDATE,'DAY') 星期二 星期几
TO_CHAR(SYSDATE,'DD') 16
TO_CHAR(SYSDATE,'DDD') 167
TO_CHAR(SYSDATE,'DL') 2009年6月16日 星期二
TO_CHAR(SYSDATE,'DS') 2009-06-16
TO_CHAR(SYSDATE,'DY') 星期二
TO_CHAR(SYSTIMESTAMP,'SS.FF3') 10.848 毫秒
TO_CHAR(SYSDATE,'FM')
TO_CHAR(SYSDATE,'FX')
TO_CHAR(SYSDATE,'HH') 03
TO_CHAR(SYSDATE,'HH24') 15
TO_CHAR(SYSDATE,'IW') 25 第几周
TO_CHAR(SYSDATE,'IYY') 009
TO_CHAR(SYSDATE,'IY') 09
TO_CHAR(SYSDATE,'J') 2454999
TO_CHAR(SYSDATE,'MI') 25
TO_CHAR(SYSDATE,'MM') 06
TO_CHAR(SYSDATE,'MON') 6月
TO_CHAR(SYSDATE,'MONTH') 6月
TO_CHAR(SYSTIMESTAMP,'PM') 下午
TO_CHAR(SYSDATE,'Q') 2 第几季度
TO_CHAR(SYSDATE,'RM') VI
TO_CHAR(SYSDATE,'RR') 09
TO_CHAR(SYSDATE,'RRRR') 2009
TO_CHAR(SYSDATE,'SS') 10
TO_CHAR(SYSDATE,'SSSSS') 55510
TO_CHAR(SYSDATE,'TS') 下午 32510
TO_CHAR(SYSDATE,'WW') 24
TO_CHAR(SYSTIMESTAMP,'W') 3
TO_CHAR(SYSDATE,'YEAR') TWO THOUSAND NINE
TO_CHAR(SYSDATE,'YYYY') 2009
TO_CHAR(SYSTIMESTAMP,'YYY') 009
TO_CHAR(SYSTIMESTAMP,'YY') 09