根据身份证号得出年龄

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());