SQL中从Timestamp时间戳提取ISO周数报错的解决方法

报错核心原因 #

所有报错基本来自三类写法错误：

• 解析日期的格式掩码和字段实际格式不匹配：你的字段显示格式为mm/dd/yyyy hh:mi:ss（月/日/年 时:分:秒），但部分写法用了dd/mm/yyyy（日/月/年）作为解析格式，遇到日期值大于12时会直接触发「无效月份」类报错。
• 冗余类型转换：to_date函数作用是将字符串类型转为日期类型，如果Datestamp本身已经是timestamp/date类型，重复套to_date会触发数据库隐式类型转换，极易抛出无效数字、无效格式类错误。
• 输出格式掩码错位：部分写法把周数截断参数和输出格式参数写混，最终输出的是日期值而非周数，拿不到预期结果。

正确写法 #

根据字段实际类型二选一即可：

1. 如果Datestamp本身是timestamp/date原生时间类型，直接调用to_char取周数即可，不需要额外嵌套转换函数：

-- iw 是Oracle内置的ISO周数专用格式掩码
to_char(Datestamp, 'iw') AS iso_week_number

2. 如果Datestamp是字符串类型，且存储内容严格遵循mm/dd/yyyy hh:mi:ss格式，需要先用完全匹配的格式掩码转为日期类型，再取周数：

to_char(
  to_date(Datestamp, 'mm/dd/yyyy hh24:mi:ss'),
  'iw'
) AS iso_week_number

原有写法的具体错误说明 #

• to_char(to_date(Datestamp, 'dd/mm/yyyy'), 'iw')：to_date的解析格式写反了月、日位置，和字段实际的mm/dd/yyyy格式不匹配，只要记录里的日期值大于12，就会抛出ORA-01843: 无效的月份错误。
• to_char(trunc(Datestamp),'iw')：如果Datestamp是原生时间类型，这个写法本身可以运行，但如果是字符串类型，trunc做隐式转换时会受会话默认日期格式影响，大概率抛出ORA-01858: 在要求输入数字的位置找到了非数字字符类错误。
• to_char(trunc(to_date(Datestamp),'iw'), 'dd/mm/yyyy hh24:mi:ss')：首先to_date未传入对应解析格式，会直接走会话默认日期配置，转换失败概率极高；其次外层to_char指定的输出格式是完整日期时间字符串，就算转换成功，输出的也是对应ISO周首日的日期值，完全不是你要的周数结果。

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