Oracle 比较日期范围结果不准确

西门吹雪@ 2018-12-04 03:40:25

select objid from szjxjg.ms_task_baseinfo m where m.tasktypecode in ('C007') and m.taskstate != '9'
and to_date(to_char(m.finishdate,'yyyy-mm-dd'),'yyyy-mm-dd') between to_date('2018-11-10','yyyy-mm-dd') and to_date('2018-11-15','yyyy-mm-dd')

结果集总有那么几个不符合这个日期范围的

CREATE TABLE MY_TABLE (
ROWNUM_ NUMBER,
OBJID VARCHAR2,
ENTID VARCHAR2,
ENTREGNO VARCHAR2,
LICENSE_NO VARCHAR2,
ENTNAME VARCHAR2,
DOM VARCHAR2,
ENTTYPE VARCHAR2,
UNEXECUTEOTHERREASON VARCHAR2,
UNEXECUTEREASON VARCHAR2,
PERNAME VARCHAR2,
JGS_NAME VARCHAR2,
LEREPNAME VARCHAR2,
VALIDITYTO VARCHAR2,
VALIDITYFROM VARCHAR2,
MOBILEINSPECTCOUNT NUMBER,
FINISHDATE VARCHAR2
);
INSERT INTO MY_TABLE(UNEXECUTEREASON, PERNAME, JGS_NAME, LEREPNAME, VALIDITYTO, VALIDITYFROM, MOBILEINSPECTCOUNT, FINISHDATE) VALUES ('已停业,已停业,查无实处', 'LXJ', '大鹏所', '唐小慧', '2021-06-15', '2016-06-16', 25, '2018-11-15');
INSERT INTO MY_TABLE(UNEXECUTEREASON, PERNAME, JGS_NAME, LEREPNAME, VALIDITYTO, VALIDITYFROM, MOBILEINSPECTCOUNT, FINISHDATE) VALUES (null, 'wys', '大鹏所', '张海燕', '2021-05-30', '2016-05-31', 19, '2018-11-14');
INSERT INTO MY_TABLE(UNEXECUTEREASON, PERNAME, JGS_NAME, LEREPNAME, VALIDITYTO, VALIDITYFROM, MOBILEINSPECTCOUNT, FINISHDATE) VALUES ('已停业,查无实处', 'L', '南澳所', '刘明伟', '2021-06-19', '2016-06-20', 16, '2018-11-15');
INSERT INTO MY_TABLE(UNEXECUTEREASON, PERNAME, JGS_NAME, LEREPNAME, VALIDITYTO, VALIDITYFROM, MOBILEINSPECTCOUNT, FINISHDATE) VALUES (null, 'wys', '大鹏所', '吴来坤', '2021-07-03', '2016-07-04', 19, '2018-11-16');
INSERT INTO MY_TABLE(UNEXECUTEREASON, PERNAME, JGS_NAME, LEREPNAME, VALIDITYTO, VALIDITYFROM, MOBILEINSPECTCOUNT, FINISHDATE) VALUES ('查无实处', '徐亚军', '大鹏所', '衷敬高', '2021-07-19', '2016-07-20', 4, '2018-11-15');
INSERT INTO MY_TABLE(UNEXECUTEREASON, PERNAME, JGS_NAME, LEREPNAME, VALIDITYTO, VALIDITYFROM, MOBILEINSPECTCOUNT, FINISHDATE) VALUES ('拒绝检查', '徐亚军', '葵涌所', '李育晓', '2021-07-10', '2016-07-11', 5, '2018-11-14');
INSERT INTO MY_TABLE(UNEXECUTEREASON, PERNAME, JGS_NAME, LEREPNAME, VALIDITYTO, VALIDITYFROM, MOBILEINSPECTCOUNT, FINISHDATE) VALUES (null, 'L', '南澳所', '郑月梅', '2021-06-30', '2016-07-01', 13, '2018-11-15');
INSERT INTO MY_TABLE(UNEXECUTEREASON, PERNAME, JGS_NAME, LEREPNAME, VALIDITYTO, VALIDITYFROM, MOBILEINSPECTCOUNT, FINISHDATE) VALUES (null, '徐亚军', '葵涌所', '陈拥军', '2021-06-28', '2016-06-29', 5, '2018-11-13');
INSERT INTO MY_TABLE(UNEXECUTEREASON, PERNAME, JGS_NAME, LEREPNAME, VALIDITYTO, VALIDITYFROM, MOBILEINSPECTCOUNT, FINISHDATE) VALUES (null, '徐亚军', '葵涌所', '韩洪敏', '2021-07-27', '2016-07-28', 5, '2018-11-27');
INSERT INTO MY_TABLE(UNEXECUTEREASON, PERNAME, JGS_NAME, LEREPNAME, VALIDITYTO, VALIDITYFROM, MOBILEINSPECTCOUNT, FINISHDATE) VALUES (null, '徐亚军', '葵涌所', '方文英', '2021-08-07', '2016-08-08', 5, '2018-11-10');
INSERT INTO MY_TABLE(UNEXECUTEREASON, PERNAME, JGS_NAME, LEREPNAME, VALIDITYTO, VALIDITYFROM, MOBILEINSPECTCOUNT, FINISHDATE) VALUES (null, '徐亚军', '葵涌所', '张文林', '2021-09-28', '2016-09-29', 5, '2018-11-13');
INSERT INTO MY_TABLE(UNEXECUTEREASON, PERNAME, JGS_NAME, LEREPNAME, VALIDITYTO, VALIDITYFROM, MOBILEINSPECTCOUNT, FINISHDATE) VALUES ('查无实处', '徐亚军', '葵涌所', '张展群', '2021-10-23', '2016-10-24', 5, '2018-11-14');
INSERT INTO MY_TABLE(UNEXECUTEREASON, PERNAME, JGS_NAME, LEREPNAME, VALIDITYTO, VALIDITYFROM, MOBILEINSPECTCOUNT, FINISHDATE) VALUES ('查无实处', '徐亚军', '葵涌所', '李丽娴', '2021-10-24', '2016-10-25', 5, '2018-11-14');
INSERT INTO MY_TABLE(UNEXECUTEREASON, PERNAME, JGS_NAME, LEREPNAME, VALIDITYTO, VALIDITYFROM, MOBILEINSPECTCOUNT, FINISHDATE) VALUES (null, '徐亚军', '葵涌所', '李凤', '2021-10-18', '2016-10-19', 5, '2018-11-12');
INSERT INTO MY_TABLE(UNEXECUTEREASON, PERNAME, JGS_NAME, LEREPNAME, VALIDITYTO, VALIDITYFROM, MOBILEINSPECTCOUNT, FINISHDATE) VALUES ('已停业,已停业', 'NHY', '南澳所', '曾浩明', '2021-10-27', '2016-10-28', 16, '2018-11-15');
INSERT INTO MY_TABLE(UNEXECUTEREASON, PERNAME, JGS_NAME, LEREPNAME, VALIDITYTO, VALIDITYFROM, MOBILEINSPECTCOUNT, FINISHDATE) VALUES ('查无实处', 'wsp', '大鹏所', '陈江标', '2021-10-27', '2016-10-28', 23, '2018-11-15');
INSERT INTO MY_TABLE(UNEXECUTEREASON, PERNAME, JGS_NAME, LEREPNAME, VALIDITYTO, VALIDITYFROM, MOBILEINSPECTCOUNT, FINISHDATE) VALUES (null, '徐亚军', '葵涌所', '方略', '2021-11-20', '2016-11-21', 5, '2018-11-10');
INSERT INTO MY_TABLE(UNEXECUTEREASON, PERNAME, JGS_NAME, LEREPNAME, VALIDITYTO, VALIDITYFROM, MOBILEINSPECTCOUNT, FINISHDATE) VALUES ('已停业,拒绝检查,查无实处,查无实处,拒绝检查,已停业,已停业,查无实处,已停业,查无实处,查无实处,查无实处,查无实处,已停业,拒绝检查,其他', '马明非', '葵涌所', '罗花', '2018-12-29', '2018-08-27', 106, '2018-12-04');

...全文