oracle日期格式比较ORA-01858

Question

我有一个关于生产的查询，直到昨天还没有问题：

SELECT E.ID       
FROM EXAM_STUD_ENTERANCE E,
EXAM_REGISTRATION R
WHERE     E.EXAM_REG_ID = R.ID
AND E.USER_NAME ='user'          
AND E.BLOCK = 1
AND E.STATUS = 1
AND TO_DATE (TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS'),
'DD/MM/YYYY HH24:MI:SS') >
TO_DATE (
TO_CHAR (R.EXAM_DATE, 'DD/MM/YYYY')
|| ' ' || R.EXAM_START_TIME
,
'DD/MM/YYYY HH24:MI:SS')
AND TO_DATE (TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS'),
'DD/MM/YYYY HH24:MI:SS') <
TO_DATE (
TO_CHAR (R.EXAM_DATE, 'DD/MM/YYYY')
|| ' ' || R.EXAM_END_TIME
,
'DD/MM/YYYY HH24:MI:SS');

但现在我收到一个错误

ORA-01858：在数字所在的位置发现了一个非数字字符预期

列EXAM_START_TIME和EXAM_END_TIME的类型为NVARCHAR2(100(，并存储类似"16:40"的数据；EXAM_DATE是日期

Answer 1

昨天有人在其中一个时间列中插入了一个错误值

进行

SELECT * FROM EDUMAN_EXAM.EXAM_REGISTRATION 
WHERE 
NOT( 
REGEXP_LIKE(EXAM_START_TIME, '^dd:dd:dd$') AND
REGEXP_LIKE(EXAM_END_TIME, '^dd:dd:dd$')
)

找到坏数据，并将其删除

注意：您发布了一个sql，指示时间列存储hh:mm:ss，您说"它正在工作"，但随后您声称时间列不存储秒。如果它们确实存储了秒，则使用上面的查询。如果不存储秒，则将正则表达式模式减少为dd:dd

作为附带说明，您不应该决定将此时间数据存储为字符串。保存EXAM_START_DATE的DATETIME可以很容易地保存时间，如果您只想要不包含时间部分的DATE，则可以TRUNC(some_datetime)删除时间组件

将数据存储在正确类型的列中，您就不会遇到这个问题，而且您也不必在数千条记录的上进入这种荒谬的date -> string -> concatenate -> parse过程