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

Calculate Age

I need to caluclate the age using the birth date. how do i go about calculating the age using reporting service?

your regrads
Angela Goh

Hello:
There are two(2) ways that I use and they are as follows - but I prefer the second method.....
1st method - in Report Properties "paste" the following code:
Public Function Age(ByVal Date_Parm)
Dim date1 as string
date1 = Date_Parm.insert(4,"/")
Dim date2 as string
date2 = date1.insert(7,"/")
Dim DateOfBirth as DateTime
DateofBirth = Date2
Dim AgeCalc as integer

Dim intDOB As Integer = _
DateOfBirth.Day + DateOfBirth.Month * 100 + DateOfBirth.Year * 10000
Dim intToday As Integer = _
DateTime.Now.Day + DateTime.Now.Month * 100 + DateTime.Now.Year * 10000
AgeCalc = (intToday - intDOB) / 10000

Return AgeCalc
End Function

Then within the field in Layout where you want to display AGE use =Code.Age(whatever the birthdate field name is)
But I prefer to (not trying to create any cotroversy here) do everything within Stored Procedures and limit any custom code within Reporting Services - So I
Create a User Defined Function within SQL - the code is as follows:
CREATE function dbo.fn_GetAge
(@.in_DOB AS datetime,@.now as datetime)
returns int
as
begin
DECLARE @.age int
IF cast(datepart(m,@.now) as int) > cast(datepart(m,@.in_DOB) as int)
SET @.age = cast(datediff(yyyy,@.in_DOB,@.now) as int)
else
IF cast(datepart(m,@.now) as int) = cast(datepart(m,@.in_DOB) as int)
IF datepart(d,@.now) >= datepart(d,@.in_DOB)
SET @.age = cast(datediff(yyyy,@.in_DOB,@.now) as int)
ELSE
SET @.age = cast(datediff(yyyy,@.in_DOB,@.now) as int) -1
ELSE
SET @.age = cast(datediff(yyyy,@.in_DOB,@.now) as int) - 1
RETURN @.age
end

Then within the stored procedure I use as the data set within Reporting Services my SQL example is:
Select PM_Birth_Date, PM_Age from Patient_Master
Where PM_Age =
Case when isdate(Pm_Birth_Date) = 1 then dbo.fn_getage(PM_Birth_Date, getdate()) else '999' end
The isdate determines if PM_Birth_Date is a valid date and if so I execute the UDF GetAge to compute the age, if Birth date is not a valid date I assign 999 as an invalid age.
Just my personal preference, but I prefer to do everything within Stored Procedures within SQL and present all data, calculations, etc. to reporting services. The only conditional formatting I conduct in RS is color of fields.
Hope this helps!
Best Regards - Joe

|||

hi Joe,

for the 1st method, where do you paste the code into the report properties, at the custom code?

your regards

Angela

|||Yes,|||

hi joe,

i try using ur SP method. but encounter some error. so like to check with you did u create a field in the table for the age? because for my db i don't store age i jux need to generate the age.

thanks

Angie

|||Hello:
Yes, we keep all kind of things in the DB (kind of inherited) which we re-compute on the "fly".
The following will work in your situation and Age ends up being a calculated "work field"
Select PM_Birth_Date,
Age = Case When isdate(PM_Birth_Date) = 1 then dbo.fn_getage (PM_Birth_Date, getdate()) else '999' END
From Patient_Master
Best Regards,
Joe|||

hi joe,
thanks for the sp. i try another way by calling the function and not using the case to check the date. bcause i encounter some error if i try using case to check for the date. therefor i skip that step n use the function directly.
thanks
Angela