在oracle中处理日期大全

orleenjrwz

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)是从当前开始下一个星期五。后面的数字是从星期日开始算起。      
1   2   3   4   5   6   7      
日   一   二   三   四   五   六   
在论坛中常常看到有对oracle中时间运算提问的问题，今天有时间，看了看以前各位兄弟的贴子，整理了一下，并作了个示例，希望会对大家有帮助。     
首先感谢ern、eric.li及各版主还有热心的兄弟们     
内容如下：     
1、oracle支持对日期进行运算     
2、日期运算时是以天为单位进行的     
3、当需要以分秒等更小的单位算值时，按时间进制进行转换即可     
4、进行时间进制转换时注意加括号（见示例中红色括号），否则会出问题     
SQL>   alter   session   set   nls_date_format='yyyy-mm-dd   hh:mi:ss';     
会话已更改。     
SQL>   set   serverout   on     
SQL>   declare     
2   DateValue   date;     
3   begin     
4   select   sysdate   into   DateValue   from   dual;     
5   dbms_output.put_line('源时间:'||to_char(DateValue));     
6   dbms_output.put_line('源时间减1天:'||to_char(DateValue-1));     
7   dbms_output.put_line('源时间减1天1小时:'||to_char(DateValue-1-1/24));     
8   dbms_output.put_line('源时间减1天1小时1分:'||to_char(DateValue-1-1/24-1/(24*60)));     
9   dbms_output.put_line('源时间减1天1小时1分1秒:'||to_char(DateValue-1-1/24-1/(24*60)-1/(24*60*6     
0)));     
10   end;     
11   /     
源时间:2003-12-29   11:53:41     
源时间减1天:2003-12-28   11:53:41     
源时间减1天1小时:2003-12-28   10:53:41     
源时间减1天1小时1分:2003-12-28   10:52:41     
源时间减1天1小时1分1秒:2003-12-28   10:52:40     
PL/SQL   过程已成功完成。     
SQL>
查看server端的字符集：
select * from V$NLS_PARAMETERS
parameter value
NLS_LANGUAGE SIMPLIFIED CHINESE
NLS_TERRITORY CHINA
…. ….
NLS_CHARACTERSET WE8MSWIN1252
NLS_SORT BINARY
Oracle通过NLS_LANG来设置客户端的情况，NLS_LANG由以下部分组成：NLS_LANG=<Language>_<Territory>.<Clients Characterset>
打开注册表:HKEY_LOCAL_MACHINE->SOFTWARE->ORACLE
修改注册表下nls_lang键值（包括oracle下的、ID0下的和HOME0下的nls_lang都修改了）
将NLS_LANG的键值设为：SIMPLIFIED CHINESE_CHINA.WE8MSWIN1252
再往数据库中插入汉字就ok了。。。
查看表的建表记录
SELECT   OBJECT_NAME,　　　　--对象名  
　OBJECT_TYPE,　　　　--对象类型  
　TO_CHAR(CREATED,   'YYYY-Mon-DD   HH24:MI')   CREATE_TIME,　--创建时间  
　TO_CHAR(LAST_DDL_TIME,   'YYYY-Mon-DD   HH24:MI')   MOD_TIME,　　   --修改时间
　TIMESTAMP,　　　　　　　　　　　　　　　　　　--时间戳  
　STATUS　　　　　　　　　　　　　　　　　　　　--状态  
　FROM   USER_OBJECTS  
　WHERE   to_char(LAST_DDL_TIME,'yyyymmdd')>'20070202';
从字符串中选择出数字
--方法一：translate函数
select translate('23456中国3-00=.,45','0123456789'||'23456中国3-00=.,45','0123456789') from dual;
--方法二：自定义函数。
create or replace function f_filter_str(var_str varchar) return varchar
is
var_str_new varchar2(2000);
begin
for i in 1..length(var_str) loop
if ascii(substr(var_str,i,1))>=48 and ascii(substr(var_str,i,1))<=57 then
var_str_new := var_str_new || substr(var_str,i,1);
end if;
end loop;
return var_str_new;
end f_filter_str;
/
select f_filter_str('23456中国3-00=.,45') from dual;
--方法三：正则表达式
--oracle10g以上版本
select regexp_replace('23456中国3-00=.,45','[^0-9]') from dual;
结果：
2345630045
select regexp_substr(regexp_replace('ASD45XXGG31KK/22','[^0-9]+','-'),'([0-9]+-?)+') from dual;
结果：
45-31-22
结果: