views:

2046

answers:

8

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.

+2  A: 
DECLARE @dateSt VARCHAR(8)
DECLARE @startDt DATETIME

-- Set the start date string
SET @dateSt = '19600518'

-- Make it a DATETIME (the ISO way)
SET @startDt = CAST(SUBSTRING(@dateSt, 1, 4) + '-' + 
   SUBSTRING(@dateSt, 5, 2) + '-' + 
   SUBSTRING(@dateSt, 7, 2)  AS DATETIME)

-- Age in Days
SELECT DATEDIFF(D, @startDt, getdate())
jrcs3
+1 for giving a quick answer, but beware - it's locale specific... It might be better to use an ISO-standard date/time
scraimer
@scraimer, but of course.
jrcs3
A: 

Here's a one-liner way to do it:

CONVERT(DATETIME, CONVERT(VARCHAR(8),19600518), 112)

But beware! This relies on T-SQL, and probably won't work in other SQL environments.

Please note that the "style" of 112 is simply the "ISO" date format of yyyymmdd. (Something I found in some CONVERT documentation.)

scraimer
There's gotta be a way to get rid of the inner CONVERT... ugh!
scraimer
Also, this doesn't answer the question - he is asking for age!
Mark Brittingham
+1  A: 

Age in years :

select datediff(YY, convert(datetime, convert(varchar, 19600518)), getdate())
Learning
This answer is incorrect...it returns the *whole* years between dates, not the age (whose boundary is on the birthdate, not Jan 1st).
Mark Brittingham
Mark: This *is* the number of years between the birthdate and *today* (aka age). Where did you see anything related to Jan 1?
Learning
Learning: He's right. Read the docs for DATEDIFF carefully
Joel Spolsky
Mark : you are correct. the boundary is implicitly Dec31 in my answer. I'll modify the answer to make it correct. thanks!
Learning
Thank you as well, Learning, for pointing out that my answer had a birthdate issue. I've used my code for several years but, with your observation as a prod, I've now updated it to use the approach that I describe below.
Mark Brittingham
A: 

I worked it out and got same as @Learning

select dob, datediff(year, convert(datetime, convert(varchar(8),[dob])) ,getdate()) as age
from  [mytable]
where IsDate(convert(varchar(8),[dob])) = 1

NB. I needed the IsDate as well as there were some invalid dates in the data.

Edit. Here is an article from SQLServerCentral on calculating age.

Paul Rowland
A word of caution: using DateDiff(year, ...) will result in an "off by one bug" for people whose birthday is after today (relative to this year, obviously).
Mark Brittingham
A: 

This is a reason why you should NOT ever store dates as anything except a datetime datatype. The best fix is to change your datatype and convert all the dates once (wouldn't be surprised if there are few invalid ones in there either). then you never have to do these workarounds again.

HLGEM
If you don't have control of the database ...
jrcs3
+4  A: 

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
LukeH
+1, simpler than my code
Michael Buen
+2  A: 

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
Mark Brittingham
this looks correct except that when you run SELECT CAST(DATEDIFF(dd, CONVERT(Datetime, CONVERT(varchar(8), 20080317), 112), GETDATE()) / 365.25 AS int) AS Age (maybe because you are dividing by 365.25 regardless)
Learning
@Mark: My code below is working now, i just forgot to declare the variables. @d should be @datetoday. Anyway, for a simpler logic on age calculation corner case, take a look at Luke's answer.
Michael Buen
A: 
[EDIT]

-- I forgot to declare the variables

declare @birthday datetime;
set @birthday = convert(datetime,convert(varchar, 19600518), 112);
declare @datetoday datetime;
set @datetoday = getdate();

select 
(
CASE
WHEN dateadd(year, datediff (year, @birthday, @datetoday), @birthday) <= @datetoday
THEN datediff (year, @birthday, @datetoday)     
ELSE datediff (year, @birthday, @datetoday) - 1
END) as age;
Michael Buen
Michael, I tried your example but it didn't work for me. I tried a few different things for @d but nothing worked.
Mark Brittingham