SQL Server 根据身份证号计算年龄

根据身份证号码去计算该用户的年龄

 1 ALTER FUNCTION [dbo].[Fn_GetAge](@IdCard NVARCHAR(18),@LimitdDate DATETIME)
 2 RETURNS INT AS 
 3 BEGIN
 4     DECLARE @BirthYear VARCHAR(4)
 5     DECLARE @BirthMonth VARCHAR(2)
 6     DECLARE @BirthDay VARCHAR(2)
 7     DECLARE @BirthDate DATETIME
 8     DECLARE @BirthDateStr NVARCHAR(10)
 9     DECLARE @Age INT
10     DECLARE @Length INT
11     SET @Age = 0
12     BEGIN
13         SET @BirthDateStr = ''
14         SET @BirthDate = NULL
15         SET @Length = LEN(@IdCard)
16         IF(@Length = 15)
17         BEGIN
18             SET @BirthYear = ('19' + SUBSTRING(@IdCard,7,2))
19             SET @BirthMonth = SUBSTRING(@IdCard,9,2) 
20             SET @BirthDay = SUBSTRING(@IdCard,11,2)
21         END
22         ELSE IF(@Length = 18)
23         BEGIN
24             SET @BirthYear = SUBSTRING(@IdCard,7,4)
25             SET @BirthMonth = SUBSTRING(@IdCard,11,2) 
26             SET @BirthDay = SUBSTRING(@IdCard,13,2) 
27         END
28         IF(@BirthYear > 1919)
29         BEGIN
30             --2月份的时间
31             IF((@BirthYear % 4 = 0 OR @BirthYear % 100 != 0) OR (@BirthYear % 400 = 0)) --闰年
32             BEGIN
33                 IF(@BirthMonth = 2)
34                 BEGIN
35                     IF(@BirthDay > 0 AND @BirthDay <= 29)
36                     BEGIN
37                         SET @BirthDateStr = @BirthYear + '-' + @BirthMonth + '-'+ @BirthDay 
38                     END
39                 END
40             END
41             ELSE
42             BEGIN
43                 IF(@BirthMonth = 2)
44                 BEGIN
45                    IF(@BirthDay > 0 AND @BirthDay <= 28)
46                    BEGIN
47                        SET @BirthDateStr = @BirthYear + '-' + @BirthMonth + '-'+ @BirthDay  
48                    END 
49                 END
50             END
51             --1 3 5 7 8 10 12月份的处理
52             IF(@BirthMonth = 1 OR @BirthMonth = 3 OR @BirthMonth = 5 OR @BirthMonth = 7 OR @BirthMonth = 8 OR @BirthMonth = 10 OR @BirthMonth = 12)
53             BEGIN
54                 IF(@BirthDay > 0 AND @BirthDay <= 31)
55                 BEGIN
56                     SET @BirthDateStr = @BirthYear + '-' + @BirthMonth + '-'+ @BirthDay 
57                 END
58             END
59             ELSE IF(@BirthMonth = 4 OR @BirthMonth = 6 OR @BirthMonth = 9 OR @BirthMonth = 11) --4 6 9 11月份的处理
60             BEGIN
61                 IF(@BirthDay > 0 AND @BirthDay <= 30)
62                 BEGIN
63                     SET @BirthDateStr = @BirthYear + '-' + @BirthMonth + '-'+ @BirthDay 
64                 END
65             END
66             IF(@BirthDateStr != '')
67             BEGIN
68                 SET @BirthDate = CONVERT(DATETIME,@BirthDateStr)
69                 SET @Age = DATEDIFF(YEAR,@BirthDate,@LimitdDate)
70                 IF(DATEPART(MONTH,@BirthDate) > DATEPART(MONTH,@LimitdDate))
71                 BEGIN
72                     SET @Age = @Age - 1
73                 END
74                 ELSE IF(DATEPART(MONTH,@BirthDate) = DATEPART(MONTH,@LimitdDate))
75                 BEGIN
76                     IF(DATEPART(DAY,@BirthDate) > DATEPART(DAY,@LimitdDate))
77                     BEGIN
78                         SET @Age = @Age - 1
79                     END
80                 END
81             END
82         END
83     END
84     RETURN @Age
85 END
View Code

 

posted @ 2019-10-12 11:18  悟否  阅读(2446)  评论(0)  编辑  收藏  举报