sql 根据省份证号码提取年龄

hive sql 根据省份证号码提取年龄

select a.sfzhm,CASE WHEN length(a.sfzhm) = 18 THEN floor(datediff(from_unixtime(unix_timestamp(), 'yyyy-MM-dd'), concat_ws('-', substr(a.sfzhm, 7, 4), substr(a.sfzhm, 11, 2), substr(a.sfzhm, 13, 2))) / 365)WHEN length(a.sfzhm) = 15 THEN floor(datediff(from_unixtime(unix_timestamp(), 'yyyy-MM-dd'), concat('19', substr(a.sfzhm, 7, 2), '-', substr(a.sfzhm, 9, 2), '-', substr(a.sfzhm, 11, 2))) / 365)ELSE NULLEND AS nlfrom dwd_xg_xslqxx a ;