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('29-feb-2000 01:02:03.122000',
'dd-mon-yyyy hh24:mi:ss.ff') dt1,
to_timestamp('15-mar-2001 11:22:33.000000',
'dd-mon-yyyy hh24:mi:ss.ff') dt2
from dual )
/结果: DAY HOUR MINUTE SECOND
---------- ---------- ---------- ----------
380 10 20 29.878select extract( year from dt2-dt1 ) as year,
extract( month from dt2-dt1 ) as month,
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('29-feb-2000 01:02:03.122000',
'dd-mon-yyyy hh24:mi:ss.ff') dt1,
to_timestamp('15-mar-2001 11:22:33.000000',
'dd-mon-yyyy hh24:mi:ss.ff') dt2
from dual )
/ERROR at line 2:
ORA-30076: invalid extract field for extract source
但加了extract( year from dt2-dt1 ) as year,和extract( month from dt2-dt1 ) as month,
执行出错
extract( hour from dt2-dt1 ) hour,
extract( minute from dt2-dt1 ) minute,
extract( second from dt2-dt1 ) second
from (select to_timestamp('29-feb-2000 01:02:03.122000',
'dd-mon-yyyy hh24:mi:ss.ff') dt1,
to_timestamp('15-mar-2001 11:22:33.000000',
'dd-mon-yyyy hh24:mi:ss.ff') dt2
from dual )
/结果: DAY HOUR MINUTE SECOND
---------- ---------- ---------- ----------
380 10 20 29.878select extract( year from dt2-dt1 ) as year,
extract( month from dt2-dt1 ) as month,
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('29-feb-2000 01:02:03.122000',
'dd-mon-yyyy hh24:mi:ss.ff') dt1,
to_timestamp('15-mar-2001 11:22:33.000000',
'dd-mon-yyyy hh24:mi:ss.ff') dt2
from dual )
/ERROR at line 2:
ORA-30076: invalid extract field for extract source
但加了extract( year from dt2-dt1 ) as year,和extract( month from dt2-dt1 ) as month,
执行出错
select extract(hour from date2) from dual
这里的date1和date2格式不一样才对。
date1和date2格式不一样才对
这句话根据你的意思是这里要两个不同日期吗
为什么这样呢
select extract( year from dt2-dt1 ) as year,
extract( month from dt2-dt1 ) as month
要extract year和month。dt2-dt1一定要是这样的东西yyyymmdd才可以吧,不然怎么成呢
select extract(year from sysdate) from dual
select extract(hour from sysdate) from dual 这个是错的
正确的就是
select extract(hour from timestamp '2008-01-17:xx:xx:xx') from dual
elect to_timestamp('29-feb-2000 01:02:03.122000', 'dd-mon-yyyy hh24:mi:ss.ff') dt1,to_timestamp('15-mar-2001 11:22:33.000000',
'dd-mon-yyyy hh24:mi:ss.ff') dt2 from dual两个timestamp相减是一个interval啊
result
---------- ---------- ----------
+000000380 10:20:29.878000000
这是原来语句执行的结果
DAY HOUR MINUTE SECOND
---------- ---------- ---------- ----------
380 10 20 29.878这两个结果可以用来作对比。就不难看出DAY的380是result中的前半部“+000000380”的绝对值。所以,想取得年和月那是不可能的。
或者可以说“+000000380”是2000年到2001年,
你所指定的日期范围内一共的天数。