oracle 日期函数介绍

在oracle中有很多关于日期的函数，如：
1 、add_months()用于从一个日期值增加或减少一些月份
date_value: = add_months(date_value,number_of_months)
例：
SQL >   select  add_months(sysdate, 12 ) " Next   Year "  from  dual;
  
Next   Year
-- --------
13 - 11月 - 04
  
SQL >   select  add_months(sysdate, 112 ) "Last  Year "  from  dual;
  
Last  Year
-- --------
13 - 3月  - 13
  
SQL >   
  
2 、 current_date ()返回当前会放时区中的当前日期
date_value: = current_date
SQL >   column  sessiontimezone  for  a15
SQL >   select  sessiontimezone, current_date   from  dual;
  
SESSIONTIMEZONE CURRENT_DA
-- ------------- ----------
+ 08 : 00            13 - 11月 - 03
  
SQL >   alter  session  set  time_zone = ' -11:00 '
    2    /
  
会话已更改。
  
SQL >   select  sessiontimezone, current_timestamp   from  dual;
  
SESSIONTIMEZONE  CURRENT_TIMESTAMP
-- ------------- ------------------------------------
- 11 : 00            12 - 11月 - 03   04.59 . 13.668000  下午  - 11 :
                  00
  
SQL >   
  
3 、 current_timestamp ()以timestamp  with  time zone数据类型返回当前会放时区中的当前日期
timestamp_with_time_zone_value: = current_timestamp ( [ timestamp_precision ] )
SQL >   column  sessiontimezone  for  a15
SQL >   column   current_timestamp  format a36
SQL >   select  sessiontimezone, current_timestamp   from  dual;
  
SESSIONTIMEZONE  CURRENT_TIMESTAMP
-- ------------- ------------------------------------
+ 08 : 00            13 - 11月 - 03   11.56 . 28.160000  上午  + 08 :
                  00
  
SQL >   alter  session  set  time_zone = ' -11:00 '
    2    /
  
会话已更改。
  
SQL >   select  sessiontimezone, current_timestamp   from  dual;
  
SESSIONTIMEZONE  CURRENT_TIMESTAMP
-- ------------- ------------------------------------
- 11 : 00            12 - 11月 - 03   04.58 . 00.243000  下午  - 11 :
                  00
  
SQL >   
  
4 、dbtimezone()返回时区
varchar_value: = dbtimezone
SQL >   select  dbtimezone  from  dual;
  
DBTIME
-- ----
- 07 : 00
  
SQL >   
  
5 、extract()找出日期或间隔值的字段值
date_value: = extract(date_field  from   [ datetime_value|interval_value ] )
SQL >   select  extract( month   from  sysdate) "This  Month "  from  dual;
  
This  Month
-- --------
          11
  
SQL >   select  extract( year   from  add_months(sysdate, 36 )) " 3  Years Out"  from  dual;
  
3  Years Out
-- ---------
         2006
  
SQL >   
  
6 、last_day()返回包含了日期参数的月份的最后一天的日期
date_value: = last_day(date_value)
SQL >   select  last_day(date ' 2000-02-01 ' ) "Leap Yr?"  from  dual;
  
Leap Yr?
-- --------
29 - 2月  - 00
  
SQL >   select  last_day(sysdate) "Last  day   of  this  month "  from  dual;
  
Last  day  o
-- --------
30 - 11月 - 03
  
SQL >   
  
7 、localtimestamp()返回会话中的日期和时间
timestamp_value: = localtimestamp
SQL >   column  localtimestamp format a28
SQL >   select  localtimestamp  from  dual;
  
LOCALTIMESTAMP
-- --------------------------
13 - 11月 - 03   12.09 . 15.433000
下午
  
SQL >   select  localtimestamp, current_timestamp   from  dual;
  
LOCALTIMESTAMP                CURRENT_TIMESTAMP
-- -------------------------- ------------------------------------
13 - 11月 - 03   12.09 . 31.006000     13 - 11月 - 03   12.09 . 31.006000  下午  + 08 :
下午                          00
&  

 



posted  on   2006 - 06 - 17   14 : 29  穷鬼 阅读( 9105 ) 评论( 6 )  编辑 收藏 所属分类: Oracle 
 
 
Comments 
#1楼  [ 楼主 ]  
团团
Oracle的解惑一二to_date()与24小时制表示法及mm分钟的显示： 
一、在使用Oracle的to_date函数来做日期转换时，很多Java程序员也许会和我一样，直觉的采用“yyyy - MM - dd HH:mm:ss”的格式作为格式进行转换，但是在Oracle中会引起错误：“ORA  01810  格式代码出现两次”。 
如： select  to_date( ' 2005-01-01 13:14:20 ' , ' yyyy-MM-dd HH24:mm:ss ' )  from  dual; 
原因是SQL中不区分大小写，MM和mm被认为是相同的格式代码，所以Oracle的SQL采用了mi代替分钟。 
select  to_date( ' 2005-01-01 13:14:20 ' , ' yyyy-MM-dd HH24:mi:ss ' )  from  dual; 

二、另要以24小时的形式显示出来要用HH24 
select  to_char(sysdate, ' yyyy-MM-dd HH24:mi:ss ' )  from  dual; // mi是分钟 
select  to_char(sysdate, ' yyyy-MM-dd HH24:mm:ss ' )  from  dual; // mm会显示月份 

Posted @  2006 - 06 - 17   14 : 38     回复  引用  查看    
#2楼  [ 楼主 ]  
团团
在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 
日 一 二 三 四 五 六 

--------------------------------------------------------------- 

select (sysdate-to_date( ' 2003 - 12 - 03   12 : 55 : 45 ' , ' yyyy - mm - dd hh24:mi:ss ' ))*24*60*60 from dual 
日期 返回的是天 然后 转换为ss

ToDate　把字符串转化为date类型 
ToChar　可以把日期转化为想要的格式的字符串