Calculate age?

I have a DOB field in my sql table, does anyone know how to display that field as an actual age in a view or a SP?select CONVERT(INTEGER, getDATE() - DOB)/365 from table|||that's only approximate, and will probably be wrong for some people on some days

;)

here's an age calculation that's correct to the day:select year(getdate())
- year(DOB)
- case when month(getdate())
> month(DOB)
then 0
else
case when month(getdate())
< month(DOB)
then 1
else
case when day(getdate())
< day(DOB)
then 1
else 0
end
end
end as age
from ...|||SELECT DATEDIFF(yy, DOB, GETDATE())

my 2 cents...|||Frettmaestro, what's your birthday?

unless it is within the first four weeks of january, it has not yet happened this year

so do your datediff calculation on your own birthday, and see what answer you get

is that your correct age?

;)|||Hmm, busted! So much for quick solutions, hehe...I would have to go with the same solution as r123456... ->

SELECT CONVERT(int, DATEDIFF(dd, '1976-03-06 13:00:000', GETDATE()))/365

But how is this only approximate...?|||it is only approximate because it totally messes up around the last day of february and/or first day of march, and it gets worse the older the person is|||This means that a 80-year old person would risk to wait 20 days before his age was updated from 79 to 80. If this is the case then I guess I at least could live with that...and I bet the 80-year old guy would be thrilled, hehehe :) Just kidding offcourse...accuracy is *very* important in all aspects of what you do.|||yeah, accuracy, what i said in my first post :cool:

here's how it works: subtract the years, then adjust it by 1 based on whether... oh, never mind, it was real easy to write, it should be real easy to figure out (hey, i should make that my sig, eh)

Calculate age using multiple fields

The database I work with uses a BirthDay, BirthMonth, and BirthYear field to hold data concerning an individual. I need to calculate their age - down to the day, if the individual is only days old, or month if the individual is months old, etc. I found a formula that got it to years, but no further.

Thanks for your help!

you can try this

DECLARE @.Person TABLE (
BirthMonth int
, BirthDate int
, BirthYear int
, BirthDay AS CONVERT(varchar(2),BirthMonth) + '/' + CONVERT(varchar(2),BirthDate) + '/' + CONVERT(varchar(4),BirthYear)
, Age AS CASE WHEN DATEDIFF(year,CONVERT(varchar(2),BirthMonth) + '/' + CONVERT(varchar(2),BirthDate) + '/' + CONVERT(varchar(4),BirthYear),GETDATE()) > 0 THEN
CONVERT(varchar,DATEDIFF(year,CONVERT(varchar(2),BirthMonth) + '/' + CONVERT(varchar(2),BirthDate) + '/' + CONVERT(varchar(4),BirthYear),GETDATE())) + ' year(s) old'
WHEN DATEDIFF(month,CONVERT(varchar(2),BirthMonth) + '/' + CONVERT(varchar(2),BirthDate) + '/' + CONVERT(varchar(4),BirthYear),GETDATE()) = 0 THEN
CONVERT(varchar,ABS(DATEDIFF(day,CONVERT(varchar(2),BirthMonth) + '/' + CONVERT(varchar(2),BirthDate) + '/' + CONVERT(varchar(4),BirthYear),GETDATE()))) + ' day(s) old'
ELSE
CONVERT(varchar,ABS(DATEDIFF(month,CONVERT(varchar(2),BirthMonth) + '/' + CONVERT(varchar(2),BirthDate) + '/' + CONVERT(varchar(4),BirthYear),GETDATE()))) + ' month(s) old'
END
)

INSERT
INTO @.Person
SELECT 10,21,2005

INSERT
INTO @.Person
SELECT 4,28,2007

INSERT
INTO @.Person
SELECT 5,20,2007

SELECT *
FROM @.person|||It works! Thank you so much!

calculate age from DOB Calculated measure

Hi All,

I am trying to calculate age from DOB and make it a calculated member.

With Member [Measures].[memberage] as

'datediff("yyyy",[Member].[DOB].[DOB],Now()) '

select {[Measures].[memberage]} on columns,

{[Member].[First Name]} on rows

from Cube

When i run this the memberage is returned as 2006 for all the DOB.

How do we calculate the age from DOB(datetime) and make it available as a calculated measure?

Thanks,

Awaiting your replies

Try

With Member [Measures].[memberage] as

'datediff("yyyy",[Member].[DOB].CurrentMember.MemberValue,Now()) '

select {[Measures].[memberage]} on columns,

{[Member].[First Name]} on rows

from Cube

|||

Hi

when i execute the above i get the following error

#Error Execution of the managed stored procedure datediff failed with the following error: Exception has been thrown by the target of an invocation.Argument 'Date1' cannot be converted to type 'Date'..

|||You have to provide an argument of type Date to DateDiff. I don't know the exact type of your [DOB] attribute. If MemberValue doesn't work in your case, you can try something like CDate([DOB].CurrentMember.Name). You need to somehow extract a Date value from the currentmember of the [DOB] hierarchy and pass it to DateDiff.

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