sql 身份证计算年龄和性别

最新推荐文章于 2023-10-27 15:56:08 发布

weixin_30810583

最新推荐文章于 2023-10-27 15:56:08 发布

阅读量551

点赞数

原文链接：http://www.cnblogs.com/yangjinwang/p/6763068.html

版权

IdentityNumber 是身份证号

年龄：

 FLOOR(DATEDIFF(DY, substring(b.IdentityNumber,7,4), GETDATE()) / 365.25)   as '推荐人年龄',

15位的身份证计算年龄：

case 
 when b.IdentityNumber IS null  or b.IdentityNumber=''  then ''
 when len(b.IdentityNumber)=18 and dbo.IsvalidIDCard(b.IdentityNumber)=1 then CAST(DATEDIFF(YEAR, substring(b.IdentityNumber,7,4), GETDATE()) AS nvarchar(20))
 when len(b.IdentityNumber)=15 and dbo.IsvalidIDCard(b.IdentityNumber)=1  then CAST(DATEDIFF(YEAR,'19'+substring(b.IdentityNumber,7,2), GETDATE()) AS nvarchar(20))
 else '' end  as '推荐人年龄',

性别：

case when (len(b.IdentityNumber)=18 and cast(substring(b.IdentityNumber,17,1) as int)%2=1)
                 or( len(b.IdentityNumber)=15 and cast(right(b.IdentityNumber,1) as int)%2=1) then  '男'
          when (len(b.IdentityNumber)=18 and cast(substring(b.IdentityNumber,17,1) as int)%2=0)
                 or( len(b.IdentityNumber)=15 and cast(right(b.IdentityNumber,1) as int)%2=0) then  '女'
            else '' end   as '推荐人性别',