SQL 从身份证号得到出生日期、年龄、男女

 

select  CONVERT(varchar(100), CONVERT(smalldatetime, SUBSTRING(b.IDCard, 7, 8)), 23) AS BrithDate_Name, 
        DATEDIFF(year, CONVERT(smalldatetime, SUBSTRING(ISNULL(b.IDCard, 0), 7, 8)), GETDATE()) AS Age_Name, 
        CASE LEFT(RIGHT(b.IDCard, 2), 1) % 2 WHEN 1 THEN '男' ELSE '女' END AS Sex_Name,
 b.IDCard from dbo.tbEmployee b where len(b.IDCard)=18

 

select  CONVERT(varchar(100), CONVERT(smalldatetime, SUBSTRING(IDCard, 7, 8)), 23) AS BrithDate_Name, 
                      DATEDIFF(year, CONVERT(smalldatetime, SUBSTRING(ISNULL(IDCard, 0), 7, 8)), GETDATE()) AS Age_Name, 
                      CASE LEFT(RIGHT(IDCard, 2), 1) % 2 WHEN 1 THEN '男' ELSE '女' END AS Sex_Name,
        IDCard,* from dbo.tbEmployee  where len(IDCard)=18