oracle 日期函数集合(集中版本)第1/2页

在oracle数据库的开发中，常因为时间的问题大费周章，所以特地将oracle数据的日期函数收藏致此。乃供他日所查也。
add_months(d,n) 日期d加n个月
last_day(d) 包含d的月?的最后一天的日期
new_time(d,a,b) a?区的日期和??d在b?区的日期和??
next_day(d,day) 比日期d?，由day指定的周几的日期
sysdate 当前的系?日期和??
greatest(d1,d2,...dn) ?出的日期列表中最后的日期
least(d1,k2,...dn) ?出的日期列表中最早的日期
to_char(d [,fmt]) 日期d按fmt指定的格式??成字符串
to_date(st [,fmt]) 字符串st按fmt指定的格式?成日期?，若fmt忽略，st要用缺省格式
round(d [,fmt]) 日期d按fmt指定格式舍入到最近的日期
trunc(d [,fmt]) 日期d按fmt指定格式截断到最近的日期
to_date 字符串类型转为换日期类型
字符串中的相应位置上的字符,必须符合时间范围的限制

查询oracle日期格式
----------------------------------


select * from nls_database_parameters;


得到结果如下表: 表中nls_date_format表示日期格式.
parameter value
----------------------------------- -----------------------------------
nls_language american
nls_territory america
nls_currency $
nls_iso_currency america
nls_numeric_characters .,
nls_characterset zhs16gbk
nls_calendar gregorian
nls_date_format dd-mon-rr
nls_date_language american
nls_sort binary
nls_time_format hh.mi.ssxff am
nls_timestamp_format dd-mon-rr hh.mi.ssxff am
nls_time_tz_format hh.mi.ssxff am tzh:tzm
nls_timestamp_tz_format dd-mon-rr hh.mi.ssxff am tzh:tzm
nls_dual_currency $
nls_comp binary
nls_nchar_characterset zhs16gbk
nls_rdbms_version 8.1.7.0.0

或者查询v$nls_parameters表,
select * from v$nls_parameters;
也有类似结果

sql>select to_date('2004-11-12 12-07-32','yyyy-mm-dd hh24-mi-ss') value from dual;
value
-------------------
2004.11.12 12:07:32

sql>select to_date('20041015') value from dual;
value
-------------------
2004.10.15 00:00:00

sql>select to_date('20041315') value from dual;
error 位于第 1 行:
ora-01861: 文字与格式字符串不匹配


sysdate 当前日期和时间

sql>select sysdate value from dual;
value
-------------------
2003.11.23 17:09:01


last_day 本月最后一天

sql>select last_day(sysdate) value from dual;
value
-------------------
2003.11.30 17:08:17


add_months(d,n) 日期d后推n个月

sql>select add_months(sysdate,2) value from dual;
value
-------------------
2005.01.23 17:10:21


next_day(d,day) 日期d之后的第一周中,指定的那天(指定星期的第几天)是什么日期

sql>select next_day(sysdate,1) value from dual;
value
-------------------
2004.11.28 17:38:55




[oracle/plsql]oracle日期处理完全版

日期处理完全版
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小时格式下时间范围为： 0:00:00 - 23:59:59....
12小时格式下时间范围为： 1:00:00 - 12:59:59 ....
1.
日期和字符转换函数用法（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
设置日期语言
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, 让后将结果相减(得到的是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 与最后一行是一样的
可以建立一个函数来处理这个问题
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.
获得小时数

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.
处理月份天数不定的办法
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'), 'dd/mm/yyyy hh:mi:ss') ,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 9:55:00 35786
sssss表示5位秒数

20.
一年的第几天
select to_char(sysdate,'ddd'),sysdate from dual
310 2002-11-6 10: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
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函数
next_day(sysdate,6)是从当前开始下一个星期五。后面的数字是从星期日开始算起。

很简单的一句话。
把oracle的日期当作一个特殊数字，以天为单位。
可以进行 日期+数字 = 日期， 日期-日期=数字，日期-数字=日期

mestamp数据的格式化显示和date 数据一样。注意，to_char函数支持date和timestamp，但是trunc却不支持timestamp数据类型。这已经清楚表明了在当两个时间的差别极度重要的情况下，使用timestamp数据类型要比date数据类型更确切。
　　
　　如果你想显示timestamp的小数秒信息，参考下面：
　　
　　1　select to_char(time1,'mm/dd/yyyy hh24:mi:ss:ff3') "date" from date_table
　　
　　date
　　
　　-----------------------
　　
　　06/20/2003 16:55:14:000
　　
　　06/26/2003 11:16:36:000
　　
　　在上例中，我只现实了小数点后3位的内容。
　　
　　计算timestamp间的数据差别要比老的date数据类型更容易。当你直接相减的话，看看会发生什么。结果将更容易理解，第一行的17天，18小时，27分钟和43秒。
　　
　　1　select time1,
　　
　　　2　　　　 time2,
　　
　　　3　　　　 substr((time2-time1),instr((time2-time1),' ')+7,2)　　　　　　　　 seconds,
　　
　　　4　　　　 substr((time2-time1),instr((time2-time1),' ')+4,2)　　　　　　　　 minutes,
　　
　　　5　　　　 substr((time2-time1),instr((time2-time1),' ')+1,2)　　　　　　　　 hours,
　　
　　　6　　　　 trunc(to_number(substr((time2-time1),1,instr(time2-time1,' '))))　 days,
　　
　　　7　　　　 trunc(to_number(substr((time2-time1),1,instr(time2-time1,' ')))/7) weeks
　　
　　　8*　 from date_table
　　
　　time1　　　　　　　　　　　 time2　　　　　　　　　　　seconds minutes hours days weeks
　　
　　-------------------------　 -------------------------- ------- ------- ----- ---- -----
　　
　　06/20/2003:16:55:14:000000　07/08/2003:11:22:57:000000　43　　 27　　　18　　17　　2
　　
　　06/26/2003:11:16:36:000000　07/08/2003:11:22:57:000000　21　　 06　　　00　　12　　1
　　
　　这就意味着不再需要关心一天有多少秒在麻烦的计算中。因此，得到天数、月数、天数、时数、分钟数和秒数就成为用substr函数摘取出数字的事情了。
　　
　　系统日期和时间
　　
　　为了得到系统时间，返回成date数据类型。你可以使用sysdate函数。
　　
　　sql> select sysdate from dual;
　　
　　为了得到系统时间，返回成timestamp数据类型。你可以使用systimpstamp函数。
　　
　　sql> select systimestamp from dual;
　　
　　你可以设置初始化参数fixed_date指定sysdate函数返回一个固定值。这用在测试日期和时间敏感的代码。注意，这个参数对于systimestamp函数无效。
　　
　　sql> alter system set fixed_date = '2003-01-01-10:00:00';
　　
　　system altered.
　　
　　sql> select sysdate from dual;
　　
　　sysdate
　　
　　---------
　　
　　01-jan-03
　　
　　sql> select systimestamp from dual;
　　
　　systimestamp
　　
　　---------------------------------------------------------
　　
　　09-jul-03 11.05.02.519000 am -06:00
　　
　　当使用date和timestamp类型的时候，选择是很清楚的。你可以随意处置date和timestamp类型。当你试图转换到更强大的timestamp的时候，需要注意，它们既有类似的地方，更有不同的地方，而足以造成破坏。两者在简洁和间隔尺寸方面各有优势，请合理地选择。


datediff( month, waterpay.copydate, getdate() ) = 1 )

datediff( day, waterpay.copydate, getdate() ) = 1 )
这两个句子的意思分别是：求以month或者以day的方式返回当前日期（getdate）减数据库中的一个日期字段（waterpay.copydate）的值等于1的记录
请问这两句在sql server中的函数如何在oracle中改写?

问题点数：20、回复次数：2
top


1 楼hevin（没有什么是不可能的）回复于 2005-09-25 18:12:10 得分 0 第一句：
months_between( to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd'),
to_date(to_char(waterpay.copydate,'yyyy-mm-dd'),'yyyy-mm-dd')
) = 1
top

2 楼hevin（没有什么是不可能的）回复于 2005-09-25 18:18:06 得分 20第二句：
to_char(sysdate - 1,'yyyy-mm-dd') = to_char(waterpay.copydate,'yyyy-mm-dd')

1