oracle这个sql怎么不对，报“文字格式与字符串不匹配”

Question

to_date(START_TIME,'YYYY-MM-DD HH24:MI:SS') >= to_date('2016-12-05 14:00:49','YYYY-MM-DD HH24:MI:SS')

Answer 1

一般出现此类问题是将日期型的字段中插入了字符型的数据，导致数据库不可识别而报错。

如：有如下test表

123

create table test(id int,begin_date date);

现在执行插入语句：

1

insert into test values (1,'2015-01-01');

则会报错：

如果正确插入，则要将字符型数据转成日期型数据：

1

insert into test values (1,to_date('2015-01-01','yyyy-mm-dd'));

Answer 2

不用to_date,换为to_char追问

to_char也是不行的
AND to_char(to_date(video.START_TIME, 'YYYY-MM-DD HH24:MI:SS'),
'YYYY-MM-DD HH24:MI:SS') >= '2016-12-05 14:07:19'

追答

to_char(START_TIME,'YYYY-MM-DD HH24:MI:SS') >= to_char('2016-12-05 14:00:49','YYYY-MM-DD HH24:MI:SS')

追问

追答

如果你的start_time是date类型的，
直接用to_char(start_time,'yyyy-mm-dd hh24:mi:ss') >= '2016-12-05 14:00:49'再试一下

追问

不对，start_time是varchar2类型。应该是start_time中有格式不规范的字段（有的日期前面有个空格，有的记录直接为空），但是这些格式不规范的字段要怎么处理呢？

追答

那直接就可以用trim(start_time) >= '2016-12-05 14:00:49'
trim是消除前后空格的函数

追问

恩恩，trim这个这么写没问题，现在最大的问题不是这个了，这里面有垃圾数据。。。。有的数据格式不是这样的。。。日，好坑

追答

我看下面你的截图 这样的话你可以先对不符合的数据更新一下
update 表名 set start_time=to_char(to_date(start_time,'yyyy-mm-dd'));
然后再执行select 条件语句

有空的数据可以用条件 and start_time is not null 筛选出来

追问

我把空的数据都做处理了，现在可以排除为空数据的影响了。但是现在这个sql：select to_date(start_time,'YYYY-MM-DD HH24:MI:SS') from T_ACCEPTED_WITNESSVIDEO
都还是报错

追答

这说明你的start_time字段中还有其他不符合日期的值存在，我执行并没有这种错误



追问

恩恩，是的，找到原因了，里面有一条不起眼的垃圾数据。谢了大婶

Answer 3

看START_TIME的值，一定是有不规范的值，若是date类型就不需要to_date，你查询下这个值的数据截个图贴出来，或者直接找追问

varchar2类型，那如果有不规范的值怎么办呢，比如说日期前面有的有个空格，有的记录是空的。

追答

空格用trim函数，记录是空的应该不用管，反正都匹配不到记录

追问

还有这样的数据怎么办。。。

追答

感觉也不是这种问题，你看我的测试数据，及输出情况



追问

这些情况好像的确没问题，那如果加上那种空的记录呢？

追答

如果考虑空记录你干脆用nvl函数，把start_time 改为nvl(start_time,'2222-12-10')给他赋个不会关联到的值，这样就使空值变非空值去关联了

追问

奇怪的是我用start_time = '' 是一条数据也查不出来的

追答

用这个trim(start_time) is null 去除空格后这个值为空

追问

这个sql还是不对，明明查出来的结果已经没有空的了，但是还是会报文字与格式字符串不匹配

追答

是啊，我开始就说过为空最多就是匹配不到符合条件的数据，不会报这种错的，你还是从数值入手找找看，当然也可能不是时间这里的问题，也可能是其他关联字段的问题，当一个数字类型关联varchar类型的话，varcha中含有非数字字符串就会报这个错，包括参数的类型，你顺便也检查下其他用到的字段

追问

恩，找到原因了。因为里面有一条不起眼的垃圾数据，唉~坑啊。感谢大婶

本回答被提问者和网友采纳

Answer 4

START_TIME 是字符串类型吗？ 如果是日期，再to_date就会报上述错误追问

START_TIME 是varchar2类型的