在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)是从当前开始下一个星期五。后面的数字是从星期日开始算起。       
  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
结果: