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

[skydqboy]

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;

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

[tianyacao007]

俺不是高手，试了，确实如你所说

[tianyacao007]

SELECT START_TIME,END_TIME,trunc(DURATION),((END_TIME - START_TIME)*86400) FROM DATETEST
WHERE trunc((END_TIME - START_TIME)*86400)+1 =DURATION;
这样有记录

[tianyacao007]

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显示

[tianyacao007]

看END_TIME，START_TIME， DURATION 是否存在空值？null与其它值判断都为false

[skydqboy]

里面就我插入的二条记录,我用这个SELECT语句查询,结果这二条全查出来了,而按道理来说,应该是查不出来的.

哪个高人可以试一下,上面都有SQL语句.
truncate table DATETEST;

这样都全了

[xiaoxiao1984]

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 为一个楼主希望的精度小数