oracle 找出异常数据

需求缘由

sql执行成功，但是导出失败

执行语句

select table_name, pk,time_o,op  from (
    SELECT 'FABCAP****BANK' as table_name,
        "SYSID"  as pk ,
        OCREATETIME AS time_o,
        'i' as op
    from FABCAPAB***BANK
    where to_date(substr(OCREATETIME,1,19),'yyyy-mm-dd hh24:mi:ss') >=to_date('2024-06-14 14:11:38','yyyy-mm-dd hh24:mi:ss')
    and to_date(substr(OCREATETIME,1,19),'yyyy-mm-dd hh24:mi:ss') <=to_date('2024-06-14 14:12:44','yyyy-mm-dd hh24:mi:ss')
    UNION all
    SELECT 'FABCA***ANK' as table_name,
        "SYSID" as pk,
        OUPDATETIME as time_o,
        'u' as op
    from FABCAP***BANK
    where to_date(substr(OUPDATETIME,1,19),'yyyy-mm-dd hh24:mi:ss') >=to_date('2024-06-14 14:11:38','yyyy-mm-dd hh24:mi:ss')
    and to_date(substr(OUPDATETIME,1,19),'yyyy-mm-dd hh24:mi:ss') <=to_date('2024-06-14 14:12:44','yyyy-mm-dd hh24:mi:ss')
     ) 

 

错误提示

Oracle 异常 ORA-01861: literal does not match format string（字符串格式不匹配）

解决办法 搜索出异常数据

通过正则方式

select * from FABCAPABILITYBANK where NOT regexp_like(OCREATETIME,'^2024');