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/

http://www.aspfaq.com/2233




[quoted text, click to view]

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


[quoted text, click to view]

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

[quoted text, click to view]

Hi Nic,

Here's a function that returns the age based on given @birth_date and
@event_date values.

* For simplification, I'm assuming that the inputs contain midnight in the
time portion. If you need to support non-midnight time portion, make sure
you first set the time portion to midnight.
For example, to set the time portion of @event_date to midnight use:
DATEADD(day, DATEDIFF(day, 0, @event_date), 0)

* I'm assuming that if the date of birth is Feb 29 in a leap year, and the
event date is in a non-leap year, the birth day date in the event year is
Mar 1.

See if the following works for you (explanations below):

CREATE FUNCTION dbo.fn_age
(@birth_date AS DATETIME, @event_date AS DATETIME)
RETURNS INT
AS
BEGIN
RETURN
( SELECT diff -
CASE
WHEN fix_bd_cur > @event_date THEN 1
ELSE 0
END AS age
FROM
( SELECT
diff,
bd_cur +
CASE
WHEN DAY(@birth_date) = 29 AND DAY(bd_cur) = 28 THEN 1
ELSE 0
END AS fix_bd_cur
FROM
( SELECT
diff,
DATEADD(year, diff, @birth_date) AS bd_cur
FROM
( SELECT DATEDIFF(year, @birth_date, @event_date) AS diff
) AS D1
) AS D2
) AS D3
)
END
GO

-- Test
SELECT
birth_date, event_date,
dbo.fn_age(birth_date, event_date) AS age
FROM (SELECT
CAST('20050212' AS DATETIME) AS birth_date,
CAST('20060211' AS DATETIME) AS event_date
UNION ALL SELECT '20050212', '20060212'
UNION ALL SELECT '20040229', '20060228'
UNION ALL SELECT '20040229', '20060301'
UNION ALL SELECT '20040229', '20080228'
UNION ALL SELECT '20040229', '20080229') AS D;

birth_date event_date age
----------------------- ----------------------- -----------
2005-02-12 00:00:00.000 2006-02-11 00:00:00.000 0
2005-02-12 00:00:00.000 2006-02-12 00:00:00.000 1
2004-02-29 00:00:00.000 2006-02-28 00:00:00.000 1
2004-02-29 00:00:00.000 2006-03-01 00:00:00.000 2
2004-02-29 00:00:00.000 2008-02-28 00:00:00.000 3
2004-02-29 00:00:00.000 2008-02-29 00:00:00.000 4

* The innermost derived table D1 calculates [diff], which is the difference
in years between @birth_date and @event_date.

* The derived table D2 calculates [bd_cur], which is @birth_date + [diff]
years. That is, [bd_cur] is the birth day date in the event year, relying on
the DATEADD function's treatment of leap years.
If you use DATEADD to add n years to a Feb 29 date, and the target year is a
leap year, you get Feb 29 in the target year. However, if the target year is
a non leap year, you get Feb 28 in the target year. The latter needs special
treatment, and is handled in the derived table D3.

* The derived table D3 "fixes" [bd_cur] by adding 1 day in the special case
where @birth_date has Feb 29 and the target year is a non leap year. In such
a case, [fix_bd_cur] becomes Mar 1.

* The outermost query returns [diff] as the age if [fix_bd_cur] <=
@birth_date, and [diff] - 1 year if [fix_bd_cur] > @birth_date.

Let me know if you have any questions.

--
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.


[quoted text, click to view]

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.


[quoted text, click to view]

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

[quoted text, click to view]

I *think* this should work, I can't seem to think of a case where it
wouldn't:

SELECT DATEDIFF(day, birthdate, eventdate) / 365

[quoted text, click to view]

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



[quoted text, click to view]

Here's what I do:

I start with a datediff:

declare @years int, @age int
select @years = datediff(year,@dob,@now)
--And then I see if their birthday hasn't passed yet.
select @age = @years - case when dateadd(year,@years,@dob) > @now then 1
else 0 end
--Of course this can be simplified into a single statement, I just find it's
easier to read if I find @years first.

And... this assumes that someone born on a leapyear celebrates their
birthday on the last of February. My reasoning is because of this:

select dateadd(day,n.num,'1-feb-1984'),
dateadd(year,1,dateadd(day,n.num,'1-feb-1984'))
from nums n
where n.num between 25 and 35
--which shows that Feb29 + 1 year = Feb28.

I'm not sure how most leap-year-kids celebrate their birthday.

Rob


[quoted text, click to view]

See if this helps:
http://groups.google.com/group/microsoft.public.sqlserver.server/msg/87103ba7349c2ec8

--
Anith

How about this variation of your last solution...

DATEDIFF(year, '20081231', '20091231')
- CASE
WHEN datepart(month,'20081231') * 100 + datepart(day,'20081231') >
datepart(month, '20091231') * 100 + datepart(day, '20091231') THEN 1
ELSE 0
END

Rather than counting the days, we just take the month and year as an
integer, and compare those. It should work just as well with February 29th
too.


[quoted text, click to view]

Gah!
I hate when people say that. =P

People born on February 29th are still the same age in every sense, except
that they don't celebrate birthdays. No one ever says "I am 36 birthdays
old", but rather "I am 36 years old." Age is a measurement of how many
months, years, etc. have passed since your birth, not how many birthdays you
have had. Of course those who are sensitive to their age may enjoy aging at
one fourth the normal rate. I suppose it is easier than turning 29 for 20
years in a row...

That said, I seem to remember a story of how the ancient Mayans took a
harsher stance on extra days in the year. They knew exactly how long the
year was, but because numbers played an important role in their beliefs,
they only acknowledged 360 days in the year. The extra 5 were there, but
not part of any month. Anyone born after the last day of the year, but
before the first day of the next year was considered not to exist. Kinda
makes me link the february 29th thing a bit more...

[quoted text, click to view]

Actually, Aaron's example is a bit cleaner...

SELECT DATEDIFF
(
YEAR,
@birthdate,
@endDate
) - CASE
WHEN 100 * MONTH(@endDate) + DAY(@endDate)
< 100 * MONTH(@birthdate) + DAY(@birthdate)
THEN 1 ELSE 0 END

[quoted text, click to view]