oracle extract 函数简介

发布时间：2020-12-12 13:45:10 所属栏目：百科来源：网络整理

导读：oracle中extract()函数从oracle 9i中引入,用于从一个 date 或者interval类型中截取到特定的部分?? //语法如下：?? EXTRACT (?? ???????? { YEAR ? | MONTH ? | DAY ? | HOUR ? | MINUTE ? | SECOND ? }?? ???????? | { TIMEZONE_HOUR | TIMEZONE_MINUTE }??

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;?//获取下一年度

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!