根据身份证号得出年龄

最新推荐文章于 2024-07-15 02:56:20 发布

转载最新推荐文章于 2024-07-15 02:56:20 发布 · 2.5k 阅读

13 篇文章

订阅专栏

update @T
set nl = case when len(ltrim(rtrim(sfz))) = 18 then cast(Year(GetDate()) as int)- cast(substring(ltrim(rtrim(sfz)),7,4) as int)
  else cast(Year(GetDate()) as int)- cast('19'+substring(ltrim(rtrim(sfz)),7,2) as int) end

#字符串格式化为日期格式
SELECT DATE_FORMAT('19841201','%Y-%m-%d')
 
#---知道出生日期求年龄
SELECT TIMESTAMPDIFF(YEAR,DATE_FORMAT('19841201','%Y-%m-%d'),CURDATE());
 
#截取身份证号中的年月日
SELECT SUBSTR('152824197810116015',7,8);
SELECT SUBSTR('152824781011601',7,6)
 
#连接字符串
SELECT CONCAT('33','22')
 
#截取身份证号中的年月日 1
SELECT  IF (LENGTH('152824197810116015')=18,
            SUBSTR('152824197810116015',7,8),
             CONCAT('19',SUBSTR('152824197810116015',7,6))
                    );
#截取身份证号中的年月日 2
SELECT  IF (LENGTH('152824781011601')=18,
            SUBSTR('152824781011601',7,8),
             CONCAT('19',SUBSTR('152824781011601',7,6))
                    );
#根据身份证号计算年龄
SELECT TIMESTAMPDIFF(YEAR,
     DATE_FORMAT( IF (LENGTH('152824781011601')=18,
            SUBSTR('152824781011601',7,8),
             CONCAT('19',SUBSTR('152824781011601',7,6))
                    ),'%Y-%m-%d'), 
     CURDATE());