Oracle执行查询遇ORA-01821错误：日期格式无法识别

你的错误原因是RFI_DATE字段的字符串格式与to_date指定的格式掩码不匹配：

• 存储的字符串包含T分隔符、3位毫秒（.064）和时区偏移（+05:30）
• 你使用的YYYY-MM-DD HH24:MI:SS格式掩码无法识别这些额外内容，导致Oracle解析失败

以下是几种针对性的解决方案：

方案1：仅提取日期时间（忽略毫秒和时区） #

如果不需要毫秒和时区信息，可以通过两种方式处理：

方式A：截取字符串到秒级 #

直接截断掉T之后秒部分以外的内容，再用匹配的格式掩码转换：

select sap_id, to_date(substr(RFI_DATE, 1, 19), 'YYYY-MM-DD"T"HH24:MI:SS') 
from IPCOLO_IPFEE_CALC_BIL;

方式B：用完整格式掩码匹配（自动截断多余内容） #

Oracle允许格式掩码完全匹配字符串结构，多余的字符会被自动忽略：

select sap_id, to_date(RFI_DATE, 'YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM') 
from IPCOLO_IPFEE_CALC_BIL;

注意：to_date不支持时区转换，这里的TZH:TZM只是用来匹配字符串中的时区偏移符号，最终得到的是基于数据库时区的日期值。

方案2：保留完整的时区和毫秒信息 #

如果需要保留原始的时区和毫秒精度，应该使用to_timestamp_tz函数转换为带时区的时间戳类型：

select sap_id, to_timestamp_tz(RFI_DATE, 'YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM') 
from IPCOLO_IPFEE_CALC_BIL;

方案3：转换为指定时区的日期 #

如果需要将带时区的时间转换为某个特定时区的日期，可以结合at time zone子句：

-- 转换为UTC时区的日期
select sap_id, cast(to_timestamp_tz(RFI_DATE, 'YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM') at time zone 'UTC' as date) 
from IPCOLO_IPFEE_CALC_BIL;

-- 转换为数据库本地时区的日期
select sap_id, cast(to_timestamp_tz(RFI_DATE, 'YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM') as date) 
from IPCOLO_IPFEE_CALC_BIL;

格式掩码说明 #

• YYYY-MM-DD：匹配年-月-日结构
• "T"：匹配字符串中的固定T分隔符（需用双引号包裹，避免被解析为格式字符）
• HH24:MI:SS：匹配24小时制的时:分:秒
• .FF3：匹配3位毫秒值
• TZH:TZM：匹配时区的小时和分钟偏移（如+05:30）

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