Calculate Age

Hi,
I have a 'date of birth' of a person and a date of an event and need to
calculate the persons age at that event (both datetime format). Is there a
function available I can use for this ?
Any advice appreciated
Nichttp://www.aspfaq.com/2233
"Niclas" <lindblom_niclas@.hotmail.com> wrote in message
news:eZ2TS%23tkGHA.1936@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a 'date of birth' of a person and a date of an event and need to
> calculate the persons age at that event (both datetime format). Is there a
> function available I can use for this ?
> Any advice appreciated
> Nic
>|||datediff(yy,dob,eventdate)
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||This approach is not always good one
select datediff(yy,'20061231','20070101')
--
Should be
SELECT DATEDIFF(year, '20061231', '20070101')
- CASE
WHEN MONTH('20061231') > MONTH('20070101')
OR MONTH('20061231') = MONTH('20070101')
AND DAY('20061231') > DAY('20070101') THEN 1
ELSE 0
END
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:9F8F5152-FBA9-4BDF-B358-01C9AEDB70C4@.microsoft.com...
> datediff(yy,dob,eventdate)
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||Thanks for poitning that out Uri.. looks like you are out to take me down
today :)
Juz joking.. u made me understand a few things.. usually I look at the holes
in the solution.. Wonder how I missed the crater :)
Anyways.. guess this should do..
SELECT DATEDIFF(year, '20061231', '20070101')
- CASE
WHEN datepart(dy,'20061231') > datepart(dy, '20070101') THEN 1
ELSE 0
END
Juz Rephrasing ur solution :)
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||That doesn't quite work because leap years have one more day than normal
years. So
SELECT DATEDIFF(year, '20071231', '20081231')
- CASE
WHEN datepart(dy,'20071231') > datepart(dy, '20081231') THEN 1
ELSE 0
END
correctly gives age as 1, but
SELECT DATEDIFF(year, '20081231', '20091231')
- CASE
WHEN datepart(dy,'20081231') > datepart(dy, '20091231') THEN 1
ELSE 0
END
gives age as 0.
Tom
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:BA42D051-F0AF-4971-8B42-6B83FE2757CC@.microsoft.com...
> Thanks for poitning that out Uri.. looks like you are out to take me down
> today :)
> Juz joking.. u made me understand a few things.. usually I look at the
> holes
> in the solution.. Wonder how I missed the crater :)
> Anyways.. guess this should do..
>
> SELECT DATEDIFF(year, '20061231', '20070101')
> - CASE
> WHEN datepart(dy,'20061231') > datepart(dy, '20070101') THEN 1
> ELSE 0
> END
> Juz Rephrasing ur solution :)
>
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
>|||Absolute murder.. better to keep my mouth shut for a while :)
So what happens if that guy was born on feb 29th?
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Haven't you ever seen any Gilbert and Sullivan?
People born on Feb 29th are only about one fourth the age that we might
think they are.
In Pirates of Penzance, which my son just performed in as the Modern Major
General, a young man was indentured to the pirates until his 21st birthday.
But it turned out he was born Feb 29, so the 21st year after he had been
born, he had only had 5 birthdays, so his servitude had to continue.
HTH
Kalen Delaney, SQL Server MVP
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:53EC76A6-9297-4057-ACD4-6FD499DEC5EE@.microsoft.com...
> Absolute murder.. better to keep my mouth shut for a while :)
> So what happens if that guy was born on feb 29th?
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
>|||Tracy,
As you probably know, a non-leap year has 365 days, and a leap year has 366
days:
Following returns 365:
SELECT DATEDIFF(day, '20030101', '20040101')
Following returns 366:
SELECT DATEDIFF(day, '20040101', '20050101')
Your expression would incorrectly return 1 for the following:
SELECT DATEDIFF(day, '20040102', '20050101') / 365
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:%23kwfIJxkGHA.4200@.TK2MSFTNGP05.phx.gbl...
> Niclas wrote:
> I *think* this should work, I can't seem to think of a case where it
> wouldn't:
> SELECT DATEDIFF(day, birthdate, eventdate) / 365
>|||Itzik Ben-Gan wrote:
> Tracy,
> As you probably know, a non-leap year has 365 days, and a leap year has 36
6
> days:
> Following returns 365:
> SELECT DATEDIFF(day, '20030101', '20040101')
> Following returns 366:
> SELECT DATEDIFF(day, '20040101', '20050101')
> Your expression would incorrectly return 1 for the following:
> SELECT DATEDIFF(day, '20040102', '20050101') / 365
>
Ahh, there's the case I didn't think of...