Oracle关于时间/日期的操作

Oracle关于时间/日期的操作

 http://gghhgame51333.blog.51cto.com/138362/126238

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