Oracle中TO_DATE/TO_TIMESTAMP处理时间格式报错及正确用法咨询

错误原因解析 #

ORA-01810 错误触发的核心原因有两个：

1. TO_DATE不支持纳秒精度：TO_DATE仅能处理到秒级，无法识别字符串中的000000000纳秒部分；
2. 格式符重复/错误：你使用的sssssssss不是Oracle的合法格式符，小数秒的正确格式符是FF（FFn可指定1-9位精度），重复定义格式符也会触发该错误。

由于你的列是时间戳类型，必须使用TO_TIMESTAMP()函数来匹配纳秒精度，以下是正确用法及示例：

正确格式及示例 #

1. 带AM/PM的12小时制时间戳转换

-- 适配你给出的字符串格式（9位纳秒+AM）
UPDATE tablename
SET date_column = TO_TIMESTAMP('12-JUN-24 09.04.32.000000000 AM', 'DD-MON-RR HH.MI.SS.FF9 AM')
WHERE -- 你的过滤条件

• RR：比YY更安全的年份格式，自动识别世纪（如'24'解析为2024，'99'解析为1999）；
• FF9：明确匹配9位纳秒精度，与你的字符串格式完全对应；
• HH：12小时制格式，配合AM区分上午/下午。

如果数据库NLS语言为中文，需显式指定月份缩写的语言：

UPDATE tablename
SET date_column = TO_TIMESTAMP(
    '12-JUN-24 09.04.32.000000000 AM', 
    'DD-MON-RR HH.MI.SS.FF9 AM', 
    'NLS_DATE_LANGUAGE=ENGLISH'
)
WHERE -- 你的过滤条件

2. 不带AM/PM的24小时制时间戳转换

-- 示例字符串：'12-JUN-24 21.04.32.000000000'（21点即晚上9点）
UPDATE tablename
SET date_column = TO_TIMESTAMP('12-JUN-24 21.04.32.000000000', 'DD-MON-RR HH24.MI.SS.FF9')
WHERE -- 你的过滤条件

• HH24：24小时制格式，无需AM/PM标识。

3. 若列是DATE类型（仅秒级精度）

如果你的列实际是DATE类型（无纳秒），可使用TO_DATE，但需去掉纳秒部分：

UPDATE tablename
SET date_column = TO_DATE('12-JUN-24 09.04.32 AM', 'DD-MON-RR HH.MI.SS AM')
WHERE -- 你的过滤条件

内容的提问来源于stack exchange，提问作者sathish kumar