尧图网络 高端网站定制 · 原创设计
免费咨询热线
400-888-6620
免费获取方案
菜鸟爬坑记之Oracle查询报错:文字与格式字符串不匹配
一般遇到这个问题是由于查询是字符串与字段类型不匹配或者日期格式不匹配导致的如1、字段类型是date类型但查询时直接与字符串进行匹配where a.DTIME2019-05-25;2、字段类型是date类型但查询是时to_date格式不正确to_date(字符串, 格式模板) 中字符串的每一段和模板标识对不上最常见 3 种场景1、年月写反模板 YYYY-DD-MM实际日期 2026-04-2804 月 28 日28 无法当作月份 MM 解析2、字符串缺时分秒但模板带 HH24:MI:SS3、分隔符不一致字符串用/模板用-或大小写、空格不匹配错误示例to_date(2026-04-28,YYYY-MM-DD HH24:MI:SS) to_date(2026/04/28,YYYY-MM-DD) to_date(26-04-28,YYYY-MM-DD)针对以上问题我们可以-- 补全时间 to_date(2026-04-28 00:00:00,YYYY-MM-DD HH24:MI:SS) -- 简化模板 to_date(2026-04-28,YYYY-MM-DD) --统一分隔符 to_date(2026/04/28,YYYY/MM/DD) --修正模板还可以使用以下方式避免-- ANSI 标准日期字面量无格式匹配问题 WHERE DTIME TIMESTAMP 2026-04-28 18:00:00 AND DTIME TIMESTAMP 2026-04-28 18:00:00 -- 仅日期筛选不带时分 WHERE DTIME DATE 2026-04-28但我遇到的问题以上方式都不能解决我们需要跟客户对接客户提供视图供我们查询由于客户数据表中存在脏数据所以客户视图查询时会报错首先我们想到可以对数据进行条件过滤把脏数据过滤掉判断是日期类型的数据才查询进视图oracle19c版本内置的有函数VALIDATE_CONVERSION可以判断字符串是否是日期格式的VALIDATE_CONVERSION(字符串 AS DATE FORMAT 格式) -- 返回1合法0非法 -- 使用示例 -- 标准年月日 SELECT VALIDATE_CONVERSION(2026-07-17 AS DATE FORMAT YYYY-MM-DD) FROM DUAL; --1 SELECT VALIDATE_CONVERSION(2026-13-01 AS DATE FORMAT YYYY-MM-DD) FROM DUAL; --0 -- 带时间 SELECT VALIDATE_CONVERSION(2026-07-17 09:20:00 AS DATE FORMAT YYYY-MM-DD HH24:MI:SS) FROM DUAL; -- 查询表批量校验 SELECT CREATE_TIME_STR, VALIDATE_CONVERSION(CREATE_TIME_STR AS DATE FORMAT YYYY-MM-DD) AS IS_DATE FROM TEST_TABLE;Oracle 12c版本的可以使用VALIDATE CONSTRAINT捕获转换异常封装函数通用判断CREATE OR REPLACE FUNCTION IS_DATE( P_STR IN VARCHAR2, P_FMT IN VARCHAR2 DEFAULT YYYY-MM-DD -- 默认日期格式 ) RETURN NUMBER IS V_DT DATE; BEGIN V_DT : TO_DATE(P_STR, P_FMT); RETURN 1; -- 合法日期 EXCEPTION WHEN OTHERS THEN RETURN 0; -- 非法日期 END IS_DATE; /使用示例-- 判断 2025-12-31 是否为 YYYY-MM-DD 日期 SELECT IS_DATE(2025-12-31) FROM DUAL; -- 返回1 SELECT IS_DATE(2025/13/01) FROM DUAL; -- 返回0 -- 指定自定义格式 yyyy/mm/dd SELECT IS_DATE(2026/07/17,yyyy/mm/dd) FROM DUAL; -- 带时分秒 SELECT IS_DATE(2026-07-17 14:30:00,yyyy-mm-dd hh24:mi:ss) FROM DUAL;但但但是客户使用的是Oracle11.2版本的没办法只能创建自定义函数进行教研代码如下CREATE OR REPLACE FUNCTION FN_CHECK_YYYYMMDD(p_str IN VARCHAR2) RETURN NUMBER IS v_d DATE; BEGIN IF p_str IS NULL OR LENGTH(TRIM(p_str)) 8 THEN RETURN 0; END IF; v_d : TO_DATE(p_str, YYYYMMDD); RETURN 1; EXCEPTION WHEN OTHERS THEN RETURN 0; END /然后视图创建语句里where 查询里 FN_CHECK_YYYYMMDD(SampleDate) 1这样修改之后视图创建语句直接执行顺利通过但是问题又来了我们直接从视图查数据时又开始报这个错文字与格式字符串不匹配内心无比崩溃然后经一番查询才知道原来WHERE 过滤是在 SELECT 转换之后执行Oracle 执行顺序FROM 关联表SELECT 里所有表达式包含to_date(a.qctime, yyyy-mm-dd)全表先执行转换再执行 WHERE 条件过滤非法qctime也就是说只要 LAS_QC_RESULT 表存在任意一条 qctime 不是合法 8 位日期的记录视图在读取时就会报 ORA-01861知道问题原因之后就好解决了查询时进行一下判断即可CASE WHEN length(REGEXP_REPLACE(a.sampleDate, [^0-9], )) 8 AND fun_get_is_dateyyyymmdd(a.sampleDate) 1 THEN to_date(a.sampleDate, yyyy-mm-dd) ELSE NULL END sampleDate修改后视图读取时不会因为脏数据崩溃非法记录 QCDateTime 为 null外部查询不受影响。至此问题解决以上如有不对的地方欢迎大佬指正
RELATED

相关推荐

托瓦兹力挺AI用于Linux开发:它就像其他工具一样有用,反对者可复刻分支

托瓦兹力挺AI用于Linux开发:它就像其他工具一样有用,反对者可复刻分支

截图与关注提示截图来自GitHub YouTube频道/ZDNET。关注ZDNET可在Google上把其设为首选来源。ZDNET要点总结有人不希望AI用于Linux的开发和维护,但托瓦兹(Torvalds)表示要忍着。如今,AI已成为Linux开发不可或缺的一部分&#xff0…

📅 2026/10/26 4:52:09
AI办公不是选工具,而是建管道:20年经验总结的4层自动化架构图,限时公开

AI办公不是选工具,而是建管道:20年经验总结的4层自动化架构图,限时公开

更多请点击: https://codechina.net 第一章:AI办公不是选工具,而是建管道:20年经验总结的4层自动化架构图,限时公开 AI办公的本质,从来不是在Copilot、Notion AI或钉钉智能助手之间做选择题,而…

📅 2026/10/16 0:59:45
2026免费视频去水印工具教程:电脑端手机端实用方法汇总

2026免费视频去水印工具教程:电脑端手机端实用方法汇总

在日常整理个人视频素材、收藏优质短视频内容的过程中,视频自带的平台角标、logo水印、内嵌字幕水印,常常会影响素材的观感与二次整理效果。2026年多款免费视频去水印工具适配了主流平台规则,覆盖电脑端、手机端、在线网页、轻量小程序等多种…

📅 2026/10/6 10:31:21
MORE NEWS

更多资讯

📰

电工证证件从哪里查询 一位焦作学员的真实经历告诉你怎么避坑

电工证证件从哪里查询 一位焦作学员的真实经历告诉你怎么避坑 不知道去哪报名怕被中介坑?这是很多想考电工证的人最真实的感受。尤其是像我这样从零开始准备考试的人,一开始对流程、机构、考试内容都一无所知,结果差点被一些不靠谱的培训机构骗了报名费,…

📰

江西新余哪里考电工证官方回应:考证不急,但别耽误找工作

江西新余哪里考电工证官方回应:考证不急,但别耽误找工作 急需特种作业证才能上岗找工作,这事儿不能拖。最近江西新余不少朋友都在问“江西新余哪里考电工证”,官方回应也已经明确,只要符合条件,考电工证并不难。但很多人不了解流程,更不清楚考试内容和…

📰

高压运行电工证用处,学历不高也能上岗必备?这几点你必须知道

高压运行电工证用处,学历不高也能上岗必备?这几点你必须知道 学历不高怕报不上名?高压运行电工证用处到底有多大?在驻马店,很多建筑工地的电工岗位,其实并不看重你是不是名校毕业,而是看你的证书和实操能力。特别是高压运行电工证,作为上岗必备的证件…

📰

人不用来轻松拿电工证内部消息全公开

人不用来轻松拿电工证内部消息全公开 怕考不过白交培训费?别慌,我这有内部消息,人不用来轻松拿电工证,关键就在这几个点上。 什么时候开始准备电工证考试最划算?…

📰

朝阳市电工证焊工证值不值得考?过期了别慌,教你如何复审延期

朝阳市电工证焊工证值不值得考?过期了别慌,教你如何复审延期 证书过期了不知道怎么复审延期?你不是一个人。很多人在拿到电工证或焊工证后,往往忽略了证书的复审时间,等到发现证书过期,才手忙脚乱地四处打听流程。尤其是在朝阳市,随着建筑、制造业等行…

📰

电工证都有什么部门的?千万别踩坑!实操考试心里没底怕挂科

电工证都有什么部门的?千万别踩坑!实操考试心里没底怕挂科 实操考试心里没底怕挂科,尤其是第一次考电工证的小伙伴,面对各种考试流程和部门,真得提前摸清楚。今天就来聊聊 电工证都有什么部门的 ,帮你避开那些 千万别踩坑…

TODAY

今日更新

THIS WEEK

本周精选

THIS MONTH

本月热门

读完文章,想聊聊您的网站?

告诉我们您的行业与需求,资深顾问一对一梳理方案与报价,全程免费。

📞 💬