SQL SERVER中根据身份证号获取出生日期

SQL SERVER中根据身份证号获取出生日期
  •  
  • 为了多次方便调用,可以在SQL SERVER中创建如下函数
  •  
  •  
  •  
  •  
  • SQL 代码   复制
  • 
    create function [dbo].[f_getBirthdayByIDCard]
    (@idcard varchar(18))
    returns datetime
    as
    begin
    declare @Idlen numeric(2,0),
     @birth varchar(10)
    set @idlen=len(@idcard)
    If @idlen=15 and SUBSTRING(@idcard,7,2)>0 and SUBSTRING(@idcard,9,2)< 13 and 
    SUBSTRING(@idcard,11,2) <32
     
    set @birth='19'+SUBSTRING(@idcard,7,2)+'-'+SUBSTRING(@idcard,9,2)+'-'+SUBSTRING(@idcard,11,2)
    
    if @idlen=18 and SUBSTRING(@idcard,11,2) < 13 and SUBSTRING(@idcard,13,2)< 32
    and SUBSTRING(@idcard,7,4)> 0
    
    set @birth=SUBSTRING(@idcard,7,4)+'-'+SUBSTRING(@idcard,11,2)+'-'+SUBSTRING(@idcard,13,2)
    If len(@birth) <> 10 
    set @birth='1900-01-01'
    return cast( convert(char(10),@birth)  as datetime)
    end
    
    				
  • 调用的时候传入身份证号码就可以了

    标签: