在oracle中处理日期大全

Sql代码

      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
结果: