在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