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' )

 

posted @   jiangyang6634  阅读(1024)  评论(0编辑  收藏  举报
(评论功能已被禁用)
相关博文:
阅读排行:
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤
点击右上角即可分享
微信分享提示