Oracle SQL查询添加日期时区时报格式不识别错误如何解决

报错根因 #

Oracle的DATE类型本身不携带时区元数据，TZH/TZM/TZD等时区相关的格式符仅支持TIMESTAMP WITH TIME ZONE类型，直接对DATE类型使用这些格式符就会触发ORA-01821: date format not recognized错误。

解决方法 #

你需要先将DATE类型转换为绑定了目标时区的TIMESTAMP WITH TIME ZONE类型，再格式化输出即可，不需要修改原表数据，正确查询语句如下：

SELECT
    TO_CHAR(
        -- 绑定美国东部时区，自动适配夏令时/冬令时
        FROM_TZ(CAST(mw.appdate AS TIMESTAMP), 'America/New_York'),
        'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM'
    ) AS APPDATE_WITH_TZ
FROM
    mwo mw

输出效果 #

原返回值23-SEP-2021 00:00:00执行以上查询后会输出ISO 8601标准的带时区格式：2021-09-23T00:00:00-04:00（9月为美国东部夏令时，时区偏移为-4，冬令时时间段会自动显示为-05:00）。

自定义格式调整 #

如果需要调整输出样式，可以修改对应参数：

• 要显示时区短名（如EST/EDT）：将TO_CHAR格式串改为'YYYY-MM-DD"T"HH24:MI:SS TZD'，输出示例：2021-09-23T00:00:00 EDT
• 要固定使用东部标准时忽略夏令时：将FROM_TZ的时区参数改为'EST'即可。

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