Oracle date字段转DD-MM-YYYY HH:MIPM格式报invalid number错误如何解决

• 核心逻辑误区：Oracle的DATE类型是二进制存储的内部结构，本身没有任何显示格式，自定义显示格式只需要在输出时通过TO_CHAR指定即可，不需要先转字符串再转回DATE的无意义操作。
• 语法错误：你当前的写法中，TO_DATE函数的输入字符串格式为YYYY-MM-DD HH:MI PM，但传入的格式掩码为DD-MM-YYYY HH:MIPM，二者完全不匹配。解析时会把字符串开头的年份位误识别为日、月份位，当解析出的月份超过1-12的合法范围时，就会抛出invalid number错误。
• 额外问题：即使格式匹配，你最终得到的还是DATE类型，显示效果会受客户端NLS_DATE_FORMAT参数控制，完全达不到你要的自定义格式效果。

方案1：直接转目标格式字符串（最常用，满足显示需求） #

如果你只是需要查询结果按指定格式展示，直接用TO_CHAR指定目标格式即可：

SELECT
    TO_CHAR(timeone,'DD-MM-YYYY HH:MIPM') AS timeone_formatted,
    TO_CHAR(timetwo,'DD-MM-YYYY HH:MIPM') AS timetwo_formatted
FROM
    my_date_table
WHERE daywork = '28-MAY-2021';

方案2：修改会话参数统一DATE显示格式 #

如果你需要返回DATE类型用于后续日期计算，可以修改当前会话的日期默认显示格式，后续所有DATE类型的查询都会按指定格式展示：

-- 修改当前会话的日期显示规则，仅对当前连接生效
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY HH:MIPM';
-- 直接查询原始DATE字段即可
SELECT
    timeone,
    timetwo
FROM
    my_date_table
WHERE daywork = '28-MAY-2021';

优化建议 #

如果daywork字段是DATE类型，建议将WHERE条件改为显式日期转换，避免因会话语言不匹配导致MAY无法识别的问题：

WHERE daywork = TO_DATE('28-MAY-2021','DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN')

内容的提问来源于stack exchange，提问作者Basudev Singh