例:select trunc(T2-T1) from ezMoniOnline where id=1提示:数据类型不一致: 应为 NUMBER, 但却获得 INTERVAL DAY TO SECOND
没用过oracle,半天没调通,谢谢大虾!!!!!!!!!!!!!!!
没用过oracle,半天没调通,谢谢大虾!!!!!!!!!!!!!!!
解决方案 »
- ORA-28547 如何解决?
- oracle11g:java.sql.SQLException: No suitable driver
- 100分向高手请教sql中in的机制
- 高手们救命啊一个关于图像处理问题
- Oracle中如何新建数据库?
- ORACLE客户端超时断开,怎么办?
- 挑战--求分页显示(普适解决方案)
- 登录PL/SQL Developer 时,提示SQL*NET没有完全安装?
- 急问这样导库有没有影响??/
- 小CASE的 SQL文请教各位,(送分的)
- 可以帮我看看 create context nscubp_context_sjpt using sjpt.urrp_login; 是什么意思吗
- rel5下安装orale11gr2时无法创建ASM磁盘
--试试这样可以不?
select trunc(to_char(T2-T1,'yyyy-mm-dd hh24:mi:ss')) from ezMoniOnline where id=1;
select trunc(to_number(substr((t2-t1),1,instr(t2-t1,' ')))) from ezMoniOnline where id=1
*
第 1 行出现错误:
ORA-01722: 无效数字
貌似好像不能转成date吧,两个timestamp类型想减是number型,怎么转成date啊,你用trunc就不行
两个timestamp类型想减是number型吗?不是INTERVAL DAY TO SECOND?
select trunc(to_number(substr((t2-t1),1,instr(t2-t1,' '))))*24*60*60
from ezMoniOnline where id=1
http://www.itmop.com/network/sql/oracle/08046587.html中的trunc(86400*(date2-date1))是否可行?
SQL> select trunc(86400*(t2-t1)) from test1;
select trunc(86400*(t2-t1)) from test1
*
第 1 行出现错误:
ORA-00932: 数据类型不一致: 应为 NUMBER, 但却获得 INTERVAL DAY TO SECOND
SQL> ed
已写入 file afiedt.buf 1* select 86400*(t2-t1) from test1
SQL> /86400*(T2-T1)
---------------------------------------------------------------------------
-002678400 00:00:00.000000000
已写入 file afiedt.buf 1 select (to_timestamp('2010-10-12 23:23:34','yyyy-mm-dd hh24:mi:ss')
2 -to_timestamp('2010-10-11 23:23:34','yyyy-mm-dd hh24:mi:ss'))*24*60*60
3* from dual
SQL> /(TO_TIMESTAMP('2010-10-1223:23:34','YYYY-MM-DDHH24:MI:SS')-TO_TIMESTAMP('20
---------------------------------------------------------------------------
+000086400 00:00:00.000000000
from ezMoniOnline
where id=1
;
基本上就这样,没有简单的方法
要最少代码的实际可以使用MONTHS_BETWEEN
SQL> select * from ezmonionline; T1 T2 ID
------------------ ---------------------------------------------------------
08-11月-10 01.42.45.656000 下午 09-11月-10 01.42.45.000000 下午 1SQL> select months_between(T1,T2)*31*24*3600 from ezmonionline;MONTHS_BETWEEN(T2,T1)*31*24*36
------------------------------
86400SQL>
select extract(day from inter) * 24 * 60 * 60 +
extract(hour from inter) * 60 * 60 +
extract(minute from inter) * 60 +
extract(second from inter) "seconds"
from (
select
to_timestamp('20100115112233','yyyymmddhh24miss') -
to_timestamp('20100101123456','yyyymmddhh24miss') inter
from dual
); seconds
----------
1205257
select trunc(to_date('2020-10-01','yyyy-mm-dd')-sysdate) from dual;
这样的日期向减,在oracle 10g中时能通过的并能得到数字类型的值的。
select
(to_date(to_char(to_timestamp('2020-10-01','yyyy-mm-dd hh:mi:ss'),'yyyy-mm-dd hh:mi:ss'),'yyyy-mm-dd hh:mi:ss')
-
to_date(to_char(to_timestamp('2010-10-01 12:22:22','yyyy-mm-dd hh:mi:ss'),'yyyy-mm-dd hh:mi:ss'),'yyyy-mm-dd hh:mi:ss')
)*24*60*60
from dual;
结果
315617858
select
(to_date(to_char(T1,'yyyy-mm-dd hh:mi:ss'),'yyyy-mm-dd hh:mi:ss')
-
to_date(to_char(T2,'yyyy-mm-dd hh:mi:ss'),'yyyy-mm-dd hh:mi:ss')
)*24*60*60
from ezmonionline;
to_char(sysdate, 'yyyy-mm-dd hh:mi:ss') d2
from dual;D1 D2
---------------------------- ----------------------------
2010-11-09 16:07:20 2010-11-09 04:07:20
timestamp+0就转为date了
date 相减就 是天数了
select trunc(
(to_timestamp('2010-10-12 23:23:34','yyyy-mm-dd hh24:mi:ss')+0)
-
(to_timestamp('2010-10-11 23:23:34','yyyy-mm-dd hh24:mi:ss')+0)
)
from dual