Oracle关于时间/日期的操作
 
1.日期时间间隔操作
当前时间减去7分钟的时间
select  sysdate,sysdate - interval '7' MINUTE  from dual
当前时间减去7小时的时间
select  sysdate - interval '7' hour  from dual
当前时间减去7天的时间
select  sysdate - interval '7' day  from dual
当前时间减去7月的时间
select  sysdate,sysdate - interval '7' month from dual
当前时间减去7年的时间
select  sysdate,sysdate - interval '7' year   from dual
时间间隔乘以一个数字
select  sysdate,sysdate - 8 *interval '2' hour   from dual
2.日期到字符操作
select  sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')  from dual
select  sysdate,to_char(sysdate,'yyyy-mm-dd hh:mi:ss')  from dual
select  sysdate,to_char(sysdate,'yyyy-ddd hh:mi:ss')  from dual
select  sysdate,to_char(sysdate,'yyyy-mm iw-d hh:mi:ss')  from dual
   参考oracle的相关关文档(ORACLE901DOC/SERVER.901/A90125/SQL_ELEMENTS4.HTM#48515)
3. 字符到日期操作
select  to_date('2003-10-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual
具体用法和上面的to_char差不多。
4. trunk/ ROUND函数的使用
select  trunc(sysdate ,'YEAR')  from dual
select  trunc(sysdate )  from dual
select  to_char(trunc(sysdate ,'YYYY'),'YYYY')  from  dual
5.oracle有毫秒级的数据类型
--返回当前时间 年月日小时分秒毫秒
select to_char(current_timestamp(5),'DD-MON-YYYY HH24:MI:SSxFF') from dual;
--返回当前 时间的秒毫秒,可以指定秒后面的精度(最大=9)
select to_char(current_timestamp(9),'MI:SSxFF') from dual;
6.计算程序运行的时间(ms)
declare
    type rc is ref cursor;
    l_rc rc;
    l_dummy all_objects.object_name%type;
    l_start number default dbms_utility.get_time;
begin
    for  I  in 1 .. 1000
    loop
        open l_rc for
          'select object_name  from all_objects '||
'where object_id = ' || i;
        fetch l_rc into l_dummy;
        close l_rc;
    end loop;
    dbms_output.put_line
    ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
      ' seconds...' );
end;

问题的提出:
如果一个表在一个date类型的字段上面建立了索引,如何使用
alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'

1: 得到当前的日期
select sysdate from dual;
 
2: 得到当天凌晨0点0分0秒的日期
select trunc(sysdate) from dual;
-- 得到这天的最后一秒
select trunc(sysdate) + 0.99999 from dual;

-- 得到小时的具体数值
select trunc(sysdate) + 1/24 from dual;
select trunc(sysdate) + 7/24 from dual;

得到明天凌晨0点0分0秒的日期
select trunc(sysdate+1) from dual;
select trunc(sysdate)+1 from dual;

3: 本月一日的日期
select trunc(sysdate,'mm') from dual;

得到下月一日的日期
select trunc(add_months(sysdate,1),'mm') from dual;
 
返回当前月的最后一天?
select last_day(sysdate)  from dual;       
select last_day(trunc(sysdate))  from dual; 
select trunc(last_day(sysdate))  from dual;
select trunc(add_months(sysdate,1),'mm') - 1 from dual;

4: 得到一年的每一天
select trunc(sysdate,'yyyy')+ rn -1 date0
from
(select rownum rn from all_objects
where rownum<366);

今天是今年的第N天
SELECT TO_CHAR(SYSDATE,'DDD') FROM DUAL;
如何在给现有的日期加上2年
select add_months(sysdate,24) from dual;
判断某一日子所在年分是否为润年
select decode(to_char(last_day(trunc(sysdate,'y')+31),'dd'),'29','闰年','平年') from dual;
判断两年后是否为润年
select decode(to_char(last_day(trunc(add_months(sysdate,24),'y')+31),'dd'),'29','闰年','平年') from dual;

5:
得到日期的季度
select ceil(to_number(to_char(sysdate,'mm'))/3) from dual;
select to_char(sysdate, 'Q') from dual;
在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位秒数     
 
关于10g的date类型:
10g:的日期格式:的dd-mon-yyyy.
但月的输入必须用日语比如:2004/10/20
应该输入:20-10月-2005
呵呵,很变态。
关于语言的设置,是环境变量的问题:
需要调整NLS_DATE_LANGUAGE, NLS_DATE_FORMAT
例如,你需要的日期格式是这样的: 01-JAN-2004
alter session set NLS_DATE_LANGUAGE='AMERICAN'
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS'
SQL> select sysdate from dual;
SYSDATE
----------
05-7月 -04
SQL> alter session set NLS_DATE_LANGUAGE='AMERICAN';
会话已更改。
SQL> select sysdate from dual;
SYSDATE
---------
05-JUL-04
 
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     
日  一  二  三  四  五  六   
 
--------------------------------------------------------------- 
 
select    (sysdate-to_date('2003-12-03  12:55:45','yyyy-mm-dd  hh24:mi:ss'))*24*60*60  from  ddual
日期  返回的是天  然后  转换为ss
取上个月月份的SQL
select decode(to_char(sysdate,'mm')-1,0,to_char(sysdate,'yyyy')-1,to_char(sysdate,'yyyy'))||case when to_number(decode(to_char(sysdate,'mm')-1,0,12,to_char(sysdate,'mm')-1)) < 10 then '0'||decode(to_char(sysdate,'mm')-1,0,12,to_char(sysdate,'mm')-1) else ''||decode(to_char(sysdate,'mm')-1,0,12,to_char(sysdate,'mm')-1) end datev from dual
程序开发: Oracle各种日期计算方法(收藏)
一个月的第一天
SELECT to_date(to_char(SYSDATE,'yyyy-mm')||'-01','yyyy-mm-dd') FROM dual
sysdate 为数据库服务器的当前系统时间。
to_char 是将日期型转为字符型的函数。
to_date 是将字符型转为日期型的函数,一般使用 yyyy-mm-dd hh24:mi:ss格式,当没有指定时间部分时,则默认时间为 00:00:00
dual 表为sys用户的表,这个表仅有一条记录,可以用于计算一些表达式,如果有好事者用 sys 用户登录系统,然后在 dual 表增加了记录的话,那么系统99.999%不能使用了。为什么使用的时候不用 sys.dual 格式呢,因为 sys 已经为 dual 表建立了所有用户均可使用的别名。

一年的第一天
SELECT to_date(to_char(SYSDATE,'yyyy')||'-01-01','yyyy-mm-dd') FROM dual
季度的第一天
SELECT to_date(to_char(SYSDATE,'yyyy-')||lpad(floor(to_number(to_char(SYSDATE,'mm'))/3)*3+1,2,'0')||'-01','yyyy-mm-dd') FROM dual
floor 为向下取整
lpad 为向左使用指定的字符扩充字符串,这个扩充字符串至2位,不足的补'0'。
当天的半夜
SELECT trunc(SYSDATE)+1-1/24/60/60
FROM dual
trunc 是将 sysdate 的时间部分截掉,即时间部分变成 00:00:00
Oracle中日期加减是按照天数进行的,所以 +1-1/24/60/60 使时间部分变成了 23:59:59。
Oracle 8i 中仅支持时间到秒,9i以上则支持到 1/100000000 秒。
上个月的最后一天
SELECT trunc(last_day(add_months(SYSDATE,-1)))+1-1/24/60/60 FROM dual
add_months 是月份加减函数。
last_day 是求该月份的最后一天的函数。
本年的最后一天
SELECT trunc(last_day(to_date(to_char(SYSDATE,'yyyy')||'-12-01','yyyy-mm-dd')))+1-1/24/60/60 FROM dual
本月的最后一天
select trunc(last_day(sysdate))+1-1/24/60/60 from dual
本月的第一个星期一
SELECT next_day(to_date(to_char(SYSDATE,'yyyy-mm')||'-01','yyyy-mm-dd'),'星期一') FROM dualnext_day 为计算从指定日期开始的第一个符合要求的日期,这里的'星期一'将根据NLS_DATE_LANGUAGE的设置稍有不同。
去掉时分秒
select trunc(sysdate) from dual
显示星期几
SELECT to_char(SYSDATE,'Day') FROM dual
取得某个月的天数
SELECT trunc(last_day(SYSDATE))- to_date(to_char(SYSDATE,'yyyy-mm')||'-01','yyyy-mm-dd')+1 FROM dual
判断是否闰年
SELECT decode(to_char(last_day(to_date(to_char(SYSDATE,'yyyy')||'-02-01','yyyy-mm-dd')),'dd'),'28','平年','闰年') FROM dual
一个季度多少天
SELECT last_day(to_date(to_char(SYSDATE,'yyyy-')||lpad(floor(to_number(to_char(SYSDATE,'mm'))/3)*3+3,2,'0')||'-01','yyyy-mm-dd')) - to_date(to_char(SYSDATE,'yyyy-')||lpad(floor(to_number(to_char(SYSDATE,'mm'))/3)*3+1,2,'0')||'-01','yyyy-mm-dd')+1 FROM dual