MSSQL 计算年龄

ALTER FUNCTION [dbo].[GetAge] ( @birthday DATETIME )
RETURNS NVARCHAR(20)
AS
BEGIN

DECLARE @startDatetime DATETIME
SET @startDatetime = @birthday

IF ( @birthday IS NOT NULL
AND ISDATE(@birthday) = 1
)
BEGIN
DECLARE @age NVARCHAR(20) ,
@y INT ,
@m INT ,
@d INT ,
@now DATETIME
SET @now = GETDATE()
SET @y = DATEDIFF(month, @birthday, GETDATE()) / 12
--SET @m = DATEDIFF(month, @birthday, GETDATE())

-- 计算月
SET @startDatetime = DATEADD(YEAR,
DATEDIFF(D, @startDatetime,
GETDATE()) / 365,
@startDatetime)

IF DAY(@startDatetime) <= DAY(GETDATE())
SET @m = DATEDIFF(M, @startDatetime, GETDATE())
ELSE
SET @m = DATEDIFF(M, @startDatetime,
DATEADD(M, -1, GETDATE()))

 

--计算天
SET @startDatetime = @birthday
SET @startDatetime = DATEADD(YEAR,
DATEDIFF(D, @startDatetime,
GETDATE()) / 365,
@startDatetime)

IF DAY(@startDatetime) <= DAY(GETDATE())
SET @startDatetime = DATEADD(M,
DATEDIFF(M, @startDatetime,
GETDATE()),
@startDatetime)


ELSE
SET @startDatetime = DATEADD(M,
DATEDIFF(M, @startDatetime,
DATEADD(M, -1,
GETDATE())),
@startDatetime)



SET @d = DATEDIFF(day, @startDatetime, GETDATE())

 

SET @age = CAST(@y AS VARCHAR(5)) + '年'
+ CAST(@m AS VARCHAR(5)) + '月' + CAST(@d AS VARCHAR(5))
+ '天'

END
ELSE
BEGIN
SET @age = NULL
END
RETURN @age
END

 

--datediff(month,'2010-05-06',getdate())%12*0.01 as 'InJobTime'