Oracle SQL中如何将VARCHAR2类型的'Mar 14 2023 12:00AM'转为DATE类型？

问题根源 #

你遇到的报错是因为TO_DATE函数的格式掩码没有覆盖输入字符串的全部内容。比如如果只指定了'Mon DD YYYY'，数据库无法解析后面的12:00AM时间部分，因此抛出date format picture ends before converting entire input string错误。

正确转换步骤 #

1. 完整解析VARCHAR2为DATE类型 #

使用匹配整个输入格式的掩码，把字符串转成DATE类型：

TO_DATE(your_date_column, 'Mon DD YYYY HH:MIAM')

各部分掩码说明：

• Mon: 匹配英文月份缩写（如Mar）
• DD: 匹配两位日期数字
• YYYY: 匹配四位年份
• HH:MIAM: 匹配12小时制的时间及AM/PM标识

2. 格式化为'MM/DD/YYYY'字符串 #

DATE类型本身没有固定显示格式，要得到指定格式的输出，需要用TO_CHAR函数将DATE类型转换为字符串：

TO_CHAR(TO_DATE(your_date_column, 'Mon DD YYYY HH:MIAM'), 'MM/DD/YYYY')

完整查询示例 #

假设你的表是your_table，日期列名为varchar_date，查询语句如下：

SELECT 
  TO_CHAR(TO_DATE(varchar_date, 'Mon DD YYYY HH:MIAM'), 'MM/DD/YYYY') AS formatted_date
FROM your_table;

额外注意 #

如果数据库的默认语言环境不支持英文月份缩写，需要在TO_DATE中指定语言参数：

TO_DATE(your_date_column, 'Mon DD YYYY HH:MIAM', 'NLS_DATE_LANGUAGE=ENGLISH')

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