SqlServer2005通过出生日期算年龄函数

 1 IF EXISTS(SELECT 1 FROM sysobjects WHERE [name] = 'GetAge')
 2     DROP FUNCTION dbo.GetAge
 3 go
 4 CREATE FUNCTION dbo.GetAge
 5 (
 6     @birthday DATETIME
 7 )
 8 RETURNS INT
 9 AS
10 BEGIN
11     IF(@birthday IS NOT NULL AND ISDATE(@birthday) = 1)
12         BEGIN
13             DECLARE @age INT , @y INT , @m INT , @d INT , @now DATETIME
14             SET @now = GETDATE()
15             SET @y = DATEPART(YEAR,@now) - DATEPART(YEAR,@birthday)
16             SET @m = DATEPART(MONTH,@now) - DATEPART(MONTH,@birthday)
17             SET @d = DATEPART(DAY,@now) - DATEPART(DAY,@birthday)
18             IF(@m = 0 AND @d = 0)
19                 BEGIN
20                     SET @age = @y
21                 END
22             ELSE
23                 BEGIN
24                     IF @m > 0 OR (@m = 0 AND @d > 0)
25                         SET @age = @y
26                     ELSE
27                         SET @age = @y - 1
28                 END
29             IF @age < 0 SET @age = 0
30         END
31     ELSE
32         BEGIN
33             SET @age = NULL
34         END
35     RETURN @age
36 END

 

转载于:https://www.cnblogs.com/ccweb/archive/2012/02/20/2359463.html