ORACLE如何获取年、月、日（Extract函数）

万望

在ORACLE中，要获得日期中的年份，例如把sysdate中的年份取出来，并不是一件难事。常用的方法是：Selectto_number(to_char(sysdate,'yyyy')) from dual，而实际上，oracle本身有更好的方法，那就是使用Extract函数，使用方法是：Select Extract(year fromsysdate) from dual，此方法获得的结果，是数值型的，这种方法省掉了类型转换，更加简洁。
相应的，要取得月份或日，可以用select extract (month from sysdate) from dual和select extract (day from sysdate) from dual。

Oracle EXTRACT函数

EXTRACT内置函数，可以处理DATATIME和INTERVAL，并从中返回各部分信息，如从TIMESTAMP返回时区，从INTERVAL返回小时/天/分钟

语法如下：

EXTRACT(YEAR|MONTH|DAY|HOUR|MINUTE|SECOND|TIMEZONE_HOUR|TIMEZONE_MINUTE|TIMEZONE_REGION|TIMEZONE_ABBRFROM expr)

1、如果返回年或月，FROM后面的expr必须是以下类型：

DATE,TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, orINTERVAL YEAR TO MONTH.

2、如果返回DAY，FROM后面的expr必须是以下类型：

DATE, TIMESTAMP, TIMESTAMP WITHTIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL DAY TO SECOND.

3、如果返回时分秒，FROM后面的expr必须是以下类型：

TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE,or INTERVAL DAY TO SECOND.

DATE类型是无效的，因为Oracle数据库把DATA类型处理为ASCI DATE，并无time可用。

4、如果返回TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_ABBR,TIMEZONE_REGION, or TIMEZONE_OFFSET，FROM后面的expr必须是以下类型：TIMESTAMP WITH TIME ZONE orTIMESTAMP WITH LOCAL TIME ZONE.

EXTRACT提取的表达式是一个ASCI的日期时间类型，而非Oracle自有的DATE类型，也就是说，只能从DATE类型的数据里面提取年、月、日。

1. select hiredate ,
   
2. extract(year from hiredate) as year,
   
3. extract(month from hiredate) as month,
   
4. extract(day from hiredate) as day  from emp;
   
5. 
   
6. select extract(hour from hiredate) as hour  from emp;
   
7. --从date类型的数据里取hour、minute、second会报错。
   
8. SQL> select extract(hour from hiredate) as hour  from emp;
   
9. select extract(hour from hiredate) as hour  from emp
   
10.                          *
    
11. ERROR at line 1:
    
12. ORA-30076: invalid extract field for extract source
    
13. 
    
14. --timestamp示例
    
15.         select dt2-dt1
    
16.      from (select to_timestamp('29-feb-2000 01:02:03.122000',
    
17.                                'dd-mon-yyyy hh24:mi:ss.ff') dt1,
    
18.                   to_timestamp('15-mar-2001 11:22:33.000000',
    
19.                                'dd-mon-yyyy hh24:mi:ss.ff') dt2
    
20.              from dual );
    
21. 
    
22. DT2-DT1
    
23. ---------------------------------------------------------------------------
    
24. +000000380 10:20:29.878000000
    
25. 
    
26. --返回了380天，10小时，20分钟，29.878秒
    
27. --而用EXTRACT更轻松
    
28. select extract(day from dt2 - dt1) day,
    
29.         extract(hour from dt2 - dt1) hour,
    
30.         extract(minute from dt2 - dt1) minute,
    
31.         extract(second from dt2 - dt1) second
    
32.    from (select to_timestamp('29-feb-2000 01:02:03.122000',
    
33.                              'dd-mon-yyyy hh24:mi:ss.ff') dt1,
    
34.                 to_timestamp('15-mar-2001 11:22:33.000000',
    
35.                              'dd-mon-yyyy hh24:mi:ss.ff') dt2
    
36.            from dual);
    
37. 
    
38.        DAY         HOUR          MINUTE     SECOND
    
39. ---------- ---------- ---------- ----------
    
40.        380           10              20     29.878

复制代码

因此这就为从日期类型的数据中抽取部分数据又多了一种选择：

1. SQL> select extract(year from sysdate) from dual;
   
2. 
   
3. EXTRACT(YEARFROMSYSDATE)
   
4. ------------------------
   
5.                     2014
   
6. 
   
7. SQL> select to_char(sysdate,'yyyy') from dual;
   
8. 
   
9. TO_C
   
10. ----
    
11. 2014
    
12. 
    
13. SQL> select to_char(sysdate,'YEAR') from dual;
    
14. 
    
15. TO_CHAR(SYSDATE,'YEAR')
    
16. ------------------------------------------
    
17. TWENTY FOURTEEN

复制代码

万望

extract这个函数oracle 9i才有的，有了它抽取日期类型数据更方便

陈晓龙

万望 发表于 2016-1-17 15:59
extract这个函数oracle 9i才有的，有了它抽取日期类型数据更方便

过时了没，oracle 9

张兴康

万望 发表于 2016-1-17 15:59
extract这个函数oracle 9i才有的，有了它抽取日期类型数据更方便

是只有oracle 9i才有，还是oracle9i之后版本都有

王爱东

这个函数我没用过 ，找时间用用。还不错。

万望

张兴康 发表于 2016-1-18 11:30
是只有oracle 9i才有，还是oracle9i之后版本都有

我的意思是从9I版本开始有的,怎么过时？

万望

取年份、月份、号想到方便

张兴康

万望 发表于 2016-1-18 23:26
我的意思是从9I版本开始有的,怎么过时？

我没说过时啊？

万望

张兴康 发表于 2016-1-20 15:07
我没说过时啊？

张兴康

万望 发表于 2016-1-20 23:49