当前位置: 代码迷 >> Oracle开发 >> ((END_TIME - START_TIME)*86400) != DURATION,为什么明明相等,但执行却不等?该如何处理
  详细解决方案

((END_TIME - START_TIME)*86400) != DURATION,为什么明明相等,但执行却不等?该如何处理

热度:142   发布时间:2016-04-24 07:56:42.0
((END_TIME - START_TIME)*86400) != DURATION,为什么明明相等,但执行却不等?
CREATE   TABLE   DATETEST
(
START_TIME   DATE,
END_TIME   DATE,
DURATION   NUMBER(6)
);
INSERT   INTO   DATETEST   VALUES(SYSDATE-60/86400,SYSDATE,60);
INSERT   INTO   DATETEST   VALUES(SYSDATE-100/86400,SYSDATE,100);

SELECT   START_TIME,END_TIME,DURATION,(END_TIME   -   START_TIME)*86400   FROM   DATETEST   WHERE   ((END_TIME   -   START_TIME)*86400)   !=   DURATION;

我这个查出来就是不相等,为什么呀,或应该怎么写?

------解决方案--------------------
SELECT START_TIME,END_TIME,trunc(DURATION),trunc((END_TIME - START_TIME)*86400, 13) FROM DATETEST
WHERE trunc((END_TIME - START_TIME)*86400)+1 =DURATION;
2007-3-1 13:41:13 2007-3-1 13:42:13 60 59.9999999999999
2007-3-1 13:40:43 2007-3-1 13:42:23 100 99.9999999999999


作为条件(END_TIME - START_TIME)*86400为 59.9999999999999
显示是作为60显示
------解决方案--------------------
SELECT START_TIME, END_TIME, DURATION, (END_TIME - START_TIME) * 86400, (END_TIME - START_TIME) *86400 - DURATION
FROM DATETEST
WHERE ((END_TIME - START_TIME) * 86400) != DURATION;

改成
SELECT START_TIME, END_TIME, DURATION, (END_TIME - START_TIME) * 86400, (END_TIME - START_TIME) *86400 - DURATION
FROM DATETEST
WHERE abs(((END_TIME - START_TIME) * 86400) - DURATION) <= &min_num;

&min_num 为一个楼主希望的精度小数