Wednesday, January 11, 2012

Determine age given date of birth using SQL

The following Microsoft SQL function will help return an integer, determining one's age given date of birth. Hope it helps.

--function to compute age given date of birth
CREATE FUNCTION fnGetAge (@d datetime)
RETURNS INT
AS
BEGIN
    DECLARE @age INT
    SET @age = (
        CASE
            WHEN
                DATEADD(YEAR, DATEDIFF (YEAR, @d, CURRENT_TIMESTAMP), @d) > CURRENT_TIMESTAMP
                THEN DATEDIFF(YEAR, @d, CURRENT_TIMESTAMP) - 1
            ELSE
                DATEDIFF(YEAR, @d, CURRENT_TIMESTAMP)
        END)
    RETURN @age
END
GO

--create table for testing
CREATE TABLE #tem (birthdate DATETIME)
go

--insert some dummy values
INSERT INTO #tem VALUES ('1981-01-09')
INSERT INTO #tem VALUES ('1981-01-10')
INSERT INTO #tem VALUES ('1981-01-11')
INSERT INTO #tem VALUES ('2000-01-01')
INSERT INTO #tem VALUES ('2012-01-01')
GO

--test our function
SELECT birthdate, dbo.fnGetAge(birthdate) FROM #tem
GO

No comments:

Post a Comment

Do provide your constructive comment. I appreciate that.