Oracle的字符串与日期互相转换函数
to_date(xxxx,'yyyy-mm-dd')是将字符串转换为日期,当出现数据类型不符合时会报文本与格式不符的错误
to_char(xxxx,'yyyy-MM-dd HH24:mi:ss')是将日期转换为指定格式的字符串,也就是年月日时分秒,select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;
业务场景,在OA数据库中存储日期字段是使用的YYYY-MM-DD HH24:MI:SS.FF3 ,但是我们是使用字符串来存储这个时间的,这样的话会转换日期to_data出错ORA-01722: invalid number
可能是因为在使用TO_DATE函数将字符类型转换为日期类型时,日期格式字符串不正确。请检查您的日期格式字符串是否与日期字符串的格式匹配。
原sql:
1 2 3 4 5 6 7 8 9 10 11 12 13 | SELECT COUNT ( CASE WHEN STATE IN ( 0, 3 ) THEN 1 END ) AS count1, COUNT ( CASE WHEN STATE = 3 THEN 1 END ) AS count2, ROUND( COUNT ( CASE WHEN STATE = 3 THEN 1 END ) / COUNT ( CASE WHEN STATE IN ( 0, 3 ) THEN 1 END ), 4 ) AS count1_div_count2 FROM "REPORT_PPP" WHERE dept_id = 124 AND TO_DATE( "REPORT_PPP" .RECORD_TIME, 'YYYY-MM-DD' ) >= TO_DATE( '2023-01-01' , 'YYYY-MM-DD' ) AND TO_DATE( "REPORT_PPP" .RECORD_TIME, 'YYYY-MM-DD' ) <= TO_DATE( '2023-03-31' , 'YYYY-MM-DD' ) |
其中
1 | RECORD_TIME存储的是字符串类型的时间,格式为2023-04-13 12:53:00.963,其中有.963这个导致无法转换为 to - date <br>试过使用YYYY-MM-DD HH24:MI:SS.FF3将毫秒数也精确进来转换<br>sql: |
SELECT COUNT( CASE WHEN STATE IN ( 0, 3 ) THEN 1 END ) AS count1, COUNT( CASE WHEN STATE = 3 THEN 1 END ) AS count2, ROUND( COUNT( CASE WHEN STATE = 3 THEN 1 END ) / COUNT( CASE WHEN STATE IN ( 0, 3 ) THEN 1 END ), 4 ) AS count1_div_count2 FROM "REPORT_PPP" WHERE dept_id = 124 AND TO_DATE( "REPORT_PPP".RECORD_TIME, 'YYYY-MM-DD HH24:MI:SS.FF3' ) >= TO_DATE( '2023-01-01', 'YYYY-MM-DD' ) AND TO_DATE( "REPORT_PPP".RECORD_TIME, 'YYYY-MM-DD HH24:MI:SS.FF3' ) <= TO_DATE( '2023-03-31', 'YYYY-MM-DD' )
仍然不行报错ORA-01821: date format not recognized
解决方案:不清除oracle转换有什么限制,故使用了一个比较笨的方法,仍然使用'YYYY-MM-DD'来转换日期,但是我使用了
TO_DATE(SUBSTR(RECORD_TIME, 1, 10 ), 'YYYY-MM-DD HH24:MI:SS')
substr函数来将record_time字段进行截取,取前十位也就刚好精确到秒,然后正常使用to_date函数转为时分秒格式
最终sql:
SELECT -- COUNT(CASE WHEN STATE IN (0,3) THEN 1 END) AS count1, -- COUNT(CASE WHEN STATE = 3 THEN 1 END) AS count2, ROUND(COUNT(CASE WHEN STATE = 3 THEN 1 END) / COUNT(CASE WHEN STATE IN (0,3) THEN 1 END), 4) AS count1_div_count2 FROM "REPORT_PPP" where TO_DATE(SUBSTR(RECORD_TIME, 1, 10 ), 'YYYY-MM-DD HH24:MI:SS') >= TO_DATE( #{startTime}, 'YYYY-MM-DD' ) and AND TO_DATE(SUBSTR(RECORD_TIME, 1, 10 ), 'YYYY-MM-DD HH24:MI:SS') <= TO_DATE( #{endTime}, 'YYYY-MM-DD' )
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤