New Function

CREATE FUNCTION dbo.AgeInYears_mvj
(
@DOB DATETIME, --Date of birth or date of manufacture
@Now DATETIME --Usually, GETDATE() or CURRENT_TIMESTAMP but
--can be any date source like a column.
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
SELECT AgeInYears =
CASE
WHEN a.[Now] < a.[DOB]
THEN null
--If birthday hasn't happended yet this year, subtract 1.
WHEN DATEADD(yy, DATEDIFF(yy, a.[DOB], a.[Now]), a.[DOB]) > a.[Now]
THEN DATEDIFF(yy, a.[DOB], a.[Now]) - 1
ELSE DATEDIFF(yy, a.[DOB], a.[Now])
END
FROM
(SELECT [DOB] = dateadd(dd,datediff(dd,0,@DOB),0),
[Now] = dateadd(dd,datediff(dd,0,@Now),0)) a
;
GO

SELECT * FROM dbo.AgeInYears_MVJ('19601104 01:00','20121104')
SELECT * FROM dbo.AgeInYears ('19601104 01:00','20121104')

SELECT * FROM dbo.AgeInYears_MVJ('19601104','19601103')
SELECT * FROM dbo.AgeInYears ('19601104','19601103')

-- here is a sample table with DOB as DATE only
DECLARE @MySample TABLE (DOB DATE)
INSERT @MySample
VALUES ('5 Nov 2012'),('3 Nov 2014'),('5 Nov 2011'),('6 Nov 2011'),('4 Apr 1973')

-- usually it would be single day and most likely it will be today...
DECLARE @DateToday DATE = GETDATE()

SELECT M.DOB
,AC.AGE
FROM @MySample M
CROSS APPLY (SELECT DATEDIFF(yy, M.DOB, @DateToday) -
CASE WHEN DOB > @DateToday THEN NULL
WHEN DATEADD(yy, DATEDIFF(yy, DOB, @DateToday), DOB) > @DateToday
THEN 1 ELSE 0
END AGE
) AC

-- here is a sample table with DOB as DATE only
DECLARE @MySample TABLE (DOB DATE)
INSERT @MySample
VALUES ('5 Nov 2012'),('3 Nov 2014'),('5 Nov 2011'),('6 Nov 2011'),('4 Apr 1973')

-- Range of dates:
DECLARE @Dates TABLE (OnDay DATE)
INSERT @Dates
VALUES ('5 Nov 2012'),('10 Nov 2020'),('5 Nov 1970'),('5 Nov 2011'),('4 Apr 2073')

SELECT M.DOB
,D.OnDay
,AC.AGE
FROM @MySample M
CROSS JOIN @Dates D
CROSS APPLY (SELECT DATEDIFF(yy, M.DOB, D.OnDay) -
CASE WHEN DOB > D.OnDay THEN NULL
WHEN DATEADD(yy, DATEDIFF(yy, DOB, D.OnDay), DOB) > D.OnDay
THEN 1 ELSE 0
END AGE
) AC
ORDER BY M.DOB, D.OnDay

Jeff Moden (11/3/2012)

Sean Lange (11/2/2012)

CELKO (11/2/2012)

If my birthday is in December (1983-12-12) and I just did the datediff function using CURRENT_TIMESTAMP and that birthdate...It would return that my age is 29 when really I would still be 28! That is why I have all those if statements! Because those account for those situations!

We have a DATE data type, the ANSI/ISO Standard CURRENT_TIMESTAMP, and we use the ISO-8601 date formats; I corrected your posting. Now it gets worse; which age system do you use? Asians count the year in which you are living (I am in my 66-th year) and Westerners count the last whole year you passed (I was 65 on 2012-01-24).

DATEDIFF (YEAR, '1983-12-12', CAST(CURRENT_TIMESTAMP AS DATE)) = 29

But:
SELECT DATEDIFF (YEAR, '1947-02-24', CAST(CURRENT_TIMESTAMP AS DATE)) = 65

Instead of all that casting and concatenation, you can use:

DATEDIFF (YEAR, @dd, CAST(CURRENT_TIMESTAMP AS DATE)),
CASE SIGN(MONTH(CURRENT_TIMESTAMP) - MONTH (@dd))
WHEN -1 THEN -1 ELSE 0 END

Little puzzle: replace the CASE expression with calls to SIGN() and ABS().

Of if you prefer the really simple method you can just get the months and do integer division.

declare @dd datetime = '1947-2-24'
select datediff(month, @dd, CURRENT_TIMESTAMP)/12

Nice try Sean, but it doesn't work in all cases. Please see the following.

DECLARE @DOB DATETIME
SET @DOB = '2008-12-31'
DECLARE @Now DATETIME
SET @Now = '2009-12-30'

select datediff(month, @DOB, @Now)/12