Calculating age in T-SQL

Post #1,925 by ChrisR 7/16/01 1:43:03 PM Reply	Calculating age in T-SQL I'm looking for the best way (short & efficient) to compute an employee's age based on a birth date and a target date. The DATEDIFF function does't seem to work properly (at least according to the way I envision it). I've figured out two other methods to compute the proper age, but they seem a bit involved for my tastes. Here's the sample code: DECLARE @birthdt DATETIME, @targetdt DATETIME SELECT @birthdt = '1/15/1930', @targetdt = '1/1/2001' SELECT wrongage0 = DATEDIFF(yy, @birthdt, @targetdt), wrongage1 = CONVERT(INT, DATEDIFF(mm, @birthdt, @targetdt) / 12), rightage0 = CONVERT(INT, CASE WHEN ((DATEPART(mm, @birthdt) = DATEPART(mm, @targetdt)) AND (DATEPART(dd, @birthdt) = DATEPART(dd, @targetdt))) THEN DATEDIFF(dd, @birthdt, @targetdt) / 365.2425 + 0.002 ELSE DATEDIFF(dd, @birthdt, @targetdt) / 365.2425 END), rightage1 = CONVERT(INT, DATEDIFF(mm, @birthdt, @targetdt) / 12) - (CASE WHEN (DATEPART(mm, @birthdt) = DATEPART(mm, @targetdt)) AND (DATEPART(dd, @birthdt) > DATEPART(dd, @targetdt)) THEN 1 ELSE 0 END) wrongage0 wrongage1 rightage0 rightage1 ----------- ----------- ----------- ----------- 71 71 70 70
Post #1,931 by dpeterson 7/16/01 2:29:58 PM Reply	Try this... I might be off, but I think this will work. -- If the day and month are less than the current day and month, the real age -- is one less than the difference of the years DECLARE @birthdate datetime, @targetdate datetime, @calc_age integer SELECT @birthdate = '1930-01-15', @targetdate = '2001-01-15', @calc_age = CONVERT(INT,DATEDIFF(yy, @birthdate, @targetdate)) SELECT 'Calculated Age' = CASE \tWHEN (day (@targetdate) >= day (@birthdate)) AND (month(@targetdate) >= month(@birthdate)) \t\tTHEN @calc_age \t\tELSE @calc_age - 1 END Darryl A. Peterson I'm not as funny as I think I am.
Post #1,978 by ChrisR 7/16/01 8:36:22 PM Reply	Rearranged a bit... For various reasons, I need to keep the computation together in a single query (the example uses @vars but the real code is grabbing a gazillion birthdays and computing the age). Anyhow, with a slight rearrangement, your code maps to something like the following: DECLARE @birthdt DATETIME, @targetdt DATETIME SELECT @birthdt = '1/15/1930', @targetdt = '12/31/2000' SELECT age = CONVERT(INT, DATEDIFF(yy, @birthdt, @targetdt)) - (CASE WHEN (MONTH(@birthdt) = MONTH(@targetdt)) AND (DAY(@birthdt) > DAY(@targetdt)) THEN 1 ELSE 0 END) I'm still wondering, though, whether there's an easier way to do this?

Welcome to IWETHEY!