pg数据库 to_date格式问题

场景

A 表字段“发票日期”类型是varchar(65)，需要转换到 B 表varchar(8)格式，字段格式要求是yyyymmdd。

实现方式是to_char(to_date(t.invoice_date,'yyyymmdd'),'yyyymmdd') AS invoice_date，

执行报错[22008] ERROR: date/time field value out of range: "2023-12-29 16:17:01"

分析

报错，2023-12-29 16:17:01 这个日期超出范围。看着日期没有问题。此时发现另外一个日期值2024-06-19 15:38:11却执行成功？？？

下面测试（使用的 pg 数据库版本是“PostgreSQL 15.6” ，官方对于格式转换的说明文档）

--运行报错，超出范围
select to_date('2023-12-29 16:17:01', 'yyyymmdd')\ 

--运行成功，但是结果是错误的！结果是2024-01-06，实际想要的结果是2024-06-19
select to_date('2024-06-19 15:38:11', 'yyyymmdd')\ 
--改成下面这样后，同样运行成功，且结果和上面相同，说明做格式转换时是根据后面的格式要求匹配的
select to_date('2024-06-', 'yyyymmdd')\ 

select to_date('2023-12-', 'yyyymmdd')\ --超出范围
select to_date('2024-09-', 'yyyymmdd')\ --运行成功，错误结果
select to_date('2024-06-', 'yyyymmdd')\ --运行成功，错误结果
select to_date('2024-05-', 'yyyymmdd')\ --运行成功，错误结果
select to_date('2024-02-', 'yyyymmdd')\ --运行成功，错误结果


--那么更改格式要求后，发现原先超出范围的日期值，现在是正确的了
select to_date('2023-12-29 16:17:01', 'yyyy-mm-dd')\ 
select to_date('2024-06-19 15:38:11', 'yyyy-mm-dd')\
select to_date('2023-1-29 16:17:01', 'yyyy-mm-dd')\

结论

始终确保输入字符串与指定的格式完全匹配。

比如字符串是2024-06-19 15:38:11，推荐使用

select to_timestamp('2023-12-29 16:17:01', 'YYYY-MM-DD HH24:MI:SS');

比如字符串是20240619，推荐使用 select to_date('20240619', 'YYYYMMDD')