In TSQL, how would you convert from an int to a datetime and give the age?

Question

What would be the sql for the following,

I have a date of birth in an int field,

ie YYYYMMDD = 19600518

I would like to get the age.

Answer 1

None of the other answers are actually calculating age. They're calculating the number of year boundaries and not taking the birthday into account.

To calculate the age, you'll need to do something like this:

DECLARE @d DATETIME
SET @d = CONVERT(DATETIME, CONVERT(VARCHAR(8), 19600518), 112)

SELECT DATEDIFF(year, @d, GETDATE())
    - CASE WHEN DATEADD(year, DATEDIFF(year, @d, GETDATE()), @d) <= GETDATE()
           THEN 0 ELSE 1 END AS Age

Answer 2

Most of the other answers are not calculating age - just whole years (e.g. Jan 1 2009 is one "year" after Dec 31 2008). Thus, if you use most of the calculations on this page, you will return an incorrect age for half of the year, on average. Luke is the only person who has seen this but his answer strikes me as too complicated - there is an easier way:

Select CAST(DATEDIFF(hh, [birthdate], GETDATE()) / 8766 AS int) AS Age

(NOTE: Thanks go to 'Learning' for making a great catch on my original algorithm - this is a revision that uses hours instead of days)

Because the rounding here is very granular, this is almost perfectly accurate for every day of every year. The exceptions are so convoluted that they are almost humorous: every fourth year the age returned will be one year too young if we A) ask for the age before 6:00 AM, B) on the person's birthday and C) their birthday is after February 28th. Of course, depending on what time someone was born this might 'technically' be correct! In my setting, this is a perfectly acceptable compromise.

Here is a loop that prints out ages to show that this works.

Declare @age int;
Declare @BirthDate datetime;
Declare @Year int;
Set @Year = 2008;
WHILE (@Year > 1930)
BEGIN
    -- Put today's date where you see '-03-18'
    SET @BirthDate = CAST(Cast(@Year as varchar(4)) + '-03-18'  AS DATETIME)
    SELECT @age=CAST(DATEDIFF(hh, @BirthDate, GETDATE()) / 8766 AS int);
    Print Cast(@Year as varchar)  + ' Age: ' + Cast(@age as varchar);
    Set @Year = @Year - 1;
END;

Finally, this is the version that will also convert Paul's integer date to a real date:

CAST(DATEDIFF(hh, Convert(Datetime, Convert(varchar(8), [birthdate]), 112), GETDATE()) / 8766 AS int) AS Age