如何在SQL中将同一列的不同格式字符串日期转换为统一格式

你之前直接使用date_format、date_parse、cast报错的核心原因是：这些函数默认只会按单一种格式解析字符串，遇到不匹配的格式就会抛出异常，必须先对不同格式的字符串做分支判断再分别解析。

通用实现逻辑 #

1. 用条件判断区分两种格式的字符串：包含-的为YYYY-MM-DD格式，包含/的为M/D/YYYY格式
2. 对两种格式分别调用对应SQL引擎的日期解析函数，指定对应输入格式完成转换
3. 最后将转换后的日期统一格式输出

不同SQL引擎实现示例 #

Hive/Spark SQL 实现 #

-- 统一转换为日期类型，格式为YYYY-MM-DD
SELECT
  CASE
    WHEN date_col LIKE '%-%' THEN to_date(date_col, 'yyyy-MM-dd')
    WHEN date_col LIKE '%/%' THEN to_date(date_col, 'M/d/yyyy')
    ELSE NULL -- 处理非法值/空值避免报错
  END AS standard_date
FROM 你的表名;

-- 如果需要输出为指定格式的字符串，比如统一为MM/dd/YYYY格式
SELECT
  date_format(
    CASE
      WHEN date_col LIKE '%-%' THEN to_date(date_col, 'yyyy-MM-dd')
      WHEN date_col LIKE '%/%' THEN to_date(date_col, 'M/d/yyyy')
      ELSE NULL
    END,
    'MM/dd/yyyy'
  ) AS standard_date_str
FROM 你的表名;

MySQL 实现 #

SELECT
  CASE
    WHEN date_col LIKE '%-%' THEN str_to_date(date_col, '%Y-%m-%d')
    WHEN date_col LIKE '%/%' THEN str_to_date(date_col, '%m/%d/%Y')
    ELSE NULL
  END AS standard_date
FROM 你的表名;

注意事项 #

• 如果需要输出其他格式，修改date_format函数的第二个参数即可
• 如果数据中存在其他格式的非法日期，可在CASE分支中新增对应判断规则，避免转换失败

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