BigQuery SQL如何验证yyyymmddhhmmss格式字符串是否为合法日期

核心逻辑分为两步：先校验字符串格式是否符合纯14位数字要求，再校验数字本身是否为合法的时间值，两步结合可以覆盖所有异常场景，包括含特殊字符、长度错误、日期逻辑无效（如2月30日、25点等）的情况。

核心校验表达式 #

直接在查询中使用如下判断逻辑即可识别有效日期：

SAFE.REGEXP_CONTAINS(待校验字段, r'^[0-9]{14}$') 
AND SAFE.PARSE_TIMESTAMP('%Y%m%d%H%M%S', 待校验字段) IS NOT NULL

返回TRUE为合法有效日期，返回FALSE或NULL为无效日期。

逻辑说明 #

• SAFE.REGEXP_CONTAINS(待校验字段, r'^[0-9]{14}$')：校验字符串是否为纯14位数字，你提到的202101....##这类含特殊字符、长度不符的异常值会直接匹配失败返回FALSE
• SAFE.PARSE_TIMESTAMP('%Y%m%d%H%M%S', 待校验字段) IS NOT NULL：严格校验14位数字是否符合时间逻辑，例如20230230120000（2月30日）、20231301120000（13月）这类格式正确但日期不存在的异常值，会解析失败返回NULL，被识别为无效
• 所有函数前缀SAFE.是为了避免输入为NULL、空值等场景下触发查询报错

使用示例 #

筛选所有无效日期的记录 #

SELECT *
FROM 你的表名
WHERE NOT (
  SAFE.REGEXP_CONTAINS(你的日期字段名, r'^[0-9]{14}$') 
  AND SAFE.PARSE_TIMESTAMP('%Y%m%d%H%M%S', 你的日期字段名) IS NOT NULL
)

封装为复用函数 #

如果需要多次调用，可以创建临时UDF简化代码：

CREATE TEMP FUNCTION IS_VALID_YYYYMMDDHHMMSS(date_str STRING)
RETURNS BOOLEAN AS (
  SAFE.REGEXP_CONTAINS(date_str, r'^[0-9]{14}$') 
  AND SAFE.PARSE_TIMESTAMP('%Y%m%d%H%M%S', date_str) IS NOT NULL
);

-- 调用示例
SELECT 
  你的日期字段名,
  IS_VALID_YYYYMMDDHHMMSS(你的日期字段名) AS is_valid_date
FROM 你的表名

注意事项 #

• 如果你的时间属于非UTC时区，可以给SAFE.PARSE_TIMESTAMP添加第三个时区参数，例如东八区使用SAFE.PARSE_TIMESTAMP('%Y%m%d%H%M%S', 待校验字段, "Asia/Shanghai")，避免时区偏移导致的解析误差
• 如果仅需校验到天的yyyymmdd格式，将正则改为r'^[0-9]{8}$'，解析函数替换为SAFE.PARSE_DATE('%Y%m%d', 待校验字段)即可

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