math martinez (11/16/2012)
SO HERE ARE THE QUERYDECLARE @DOB SMALLDATETIME
SELECT @DOB = '11/15/1987'
SELECT
CASE
WHEN MONTH(@DOB) >= MONTH(GETDATE()) AND DAY(@DOB) >=DAY(GETDATE()) THEN DATEDIFF(YY,@DOB,GETDATE())
ELSE DATEDIFF(YY,@DOB,GETDATE())-1
END AS AGE
HOPE I CAN HELP! 🙂
It also has a Leap Year bug. I changed GETDATE() in your code to @Now to make it simple to test. Notice that your code says a person is 1 year old for the given dates, which is incorrect..
DECLARE @DOB DATETIME
,@Now DATETIME
SELECT @DOB = '2000-03-31'
,@Now = '2001-03-30'
SELECT
CASE
WHEN MONTH(@DOB) >= MONTH(@Now) AND DAY(@DOB) >=DAY(@Now) THEN DATEDIFF(YY,@DOB,@Now)
ELSE DATEDIFF(YY,@DOB,@Now)-1
END AS AGE
--Jeff Moden
Change is inevitable... Change for the better is not.