oracle中extract()函数从oracle 9i中引入,用于从一个date或者interval类型中截取到特定的部分
//语法如下： EXTRACT ( { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND } | { TIMEZONE_HOUR | TIMEZONE_MINUTE } | { TIMEZONE_REGION | TIMEZONE_ABBR } FROM { date_value | interval_value } ) //我们只可以从一个date类型中截取 year,month,day(date日期的格式为yyyy-mm-dd); //我们只可以从一个 timestamp with time zone 的数据类型中截取TIMEZONE_HOUR和TIMEZONE_MINUTE; select extract(year from date'2011-05-17') year from dual; YEAR ---------- 2011 select extract(month from date'2011-05-17') month from dual; MONTH ---------- 5 select extract(day from date'2011-05-17') day from dual; DAY ---------- 17 //获取两个日期之间的具体时间间隔，extract函数是最好的选择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('2011-02-04 15:07:00','yyyy-mm-dd hh24:mi:ss') dt1 ,to_timestamp('2011-05-17 19:08:46','yyyy-mm-dd hh24:mi:ss') dt2 from dual);
/ DAY HOUR MINUTE SECOND ---------- ---------- ---------- ---------- 102 4 1 46 --select extract(year from systimestamp) year ,extract(month from systimestamp) month ,extract(day from systimestamp) day ,extract(hour from systimestamp) hour ,extract(minute from systimestamp) minute ,extract(second from systimestamp) second ,extract(timezone_hour from systimestamp) th ,extract(timezone_minute from systimestamp) tm ,extract(timezone_region from systimestamp) tr ,extract(timezone_abbr from systimestamp) ta from dual；
结果： YEAR MONTH DAY hour MINUTE SECOND TH TM TR TA ---------- ---------- ---------- ------- ---------- ---------- ------ ------ --------- ---------- 2018 9 28 14 13 38.757618 0 0 UNKNOWN UNK //获取年月日 select extract(year from sysdate) from dual; //获取当前年度 select extract(month from sysdate) from dual;//获取当前月份 select extract(day from sysdate) from dual ；//获取日 select extract(year from sysdate)+1 from dual; //获取下一年度转载于:https://www.cnblogs.com/weiyi1314/p/9718343.html
相关资源：数据结构—成绩单生成器