--用于从date或interval类型中截取特定的值
--语法如下:
EXTRACT (
{ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }
| { TIMEZONE_HOUR | TIMEZONE_MINUTE }
| { TIMEZONE_REGION | TIMEZONE_ABBR }
FROM { date_value | interval_value } )
1.date类型日期截取
从一个date类型中截取 year,month,day(date日期的格式为yyyy-mm-dd);
1)截取日期的year
SELECT extract(YEAR FROM date'2012-10-12') YEAR from;
-------2012
SELECT extract(YEAR FROM SYSDATE) YEAR FROM dual;
-------2012
2)截取日期的month
SELECT extract(MONTH FROM date'2012-10-12') YEAR from dual;
-------10
SELECT extract(MONTH FROM SYSDATE) YEAR FROM dual;
------10
3)截取日期的day
SELECT extract(DAY FROM date'2012-10-12') YEAR from dual;
---------12
SELECT extract(DAY FROM SYSDATE) YEAR FROM dual;
---------12
2.获取两个日期之间的具体时间间隔
select extract(day from dt2-dt1) day
,extract(hour from dt2-dt1) hour
,extract(minute from dt2-dt1) minute
,extract(second from dt2-dt1) second
from (
select to_timestamp('2012-02-04 15:07:00','yyyy-MM-dd hh24:mi:ss') dt1
,to_timestamp('2012-10-12 17:08:46','yyyy-MM-dd hh24:mi:ss') dt2
from dual)
---------------------------------------------
day hour minute second
251 2 1 46