身份证号提取出生年月

quweiie

于 2024-05-18 09:38:41 发布

阅读量399

点赞数 3

分类专栏： sql 文章标签：前端 linux javascript

本文链接：https://blog.csdn.net/quweiie/article/details/139018134

版权

sql 专栏收录该内容

23 篇文章

订阅专栏

UPDATE sheet1
SET birthday = STR_TO_DATE(SUBSTRING(cardID, 7, 8), '%Y%m%d')
WHERE LENGTH(cardID) = 18
  AND cardID REGEXP '^[0-9]{17}[0-9Xx]$'
  AND SUBSTRING(cardID, 11, 2) BETWEEN '01' AND '12'
  AND SUBSTRING(cardID, 13, 2) BETWEEN '01' AND CASE
      WHEN SUBSTRING(cardID, 11, 2) IN ('01', '03', '05', '07', '08', '10', '12') THEN '31'
      WHEN SUBSTRING(cardID, 11, 2) IN ('04', '06', '09', '11') THEN '30'
      WHEN SUBSTRING(cardID, 11, 2) = '02' THEN
          CASE
              WHEN (CAST(SUBSTRING(cardID, 7, 4) AS UNSIGNED) % 4 = 0 AND CAST(SUBSTRING(cardID, 7, 4) AS UNSIGNED) % 100 != 0) OR CAST(SUBSTRING(cardID, 7, 4) AS UNSIGNED) % 400 = 0 THEN '29'
              ELSE '28'
          END
  END;