Calculating age in years and months in SSRS

Question

Calculating age in years and months in SSRS

kathryn.gilbertson

Old Hand

Points: 312
More actions
April 15, 2010 at 8:23 am

#217881

Hi
I have a list of pupils in a school who have been referred to a service. I would like to show the age of the pupil at the time of the referral in years and months.
I have used the following expression to calculate the age. It gives me years but i cant seem to calculate the remainder as number of months.
=round(datediff("m",Fields!DOB.Value,Fields!REQUEST_DATE.Value)/12,2)
For a pupil who is 5 years and 11 months old I'm trying to get the output of 5.11.
I hope you can help.
Many thanks
kat

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 1

I have used the following expression to calculate the age. It gives me years but i cant seem to calculate the remainder as number of months.
=round(datediff("m",Fields!DOB.Value,Fields!REQUEST_DATE.Value)/12,2)
For a pupil who is 5 years and 11 months old I'm trying to get the output of 5.11.

Not a perfect one but this might help

=round(datediff("m",Fields!DOB.Value,Fields!REQUEST_DATE.Value)/12,2) + "."+ round(datediff("m",Fields!DOB.Value,Fields!REQUEST_DATE.Value)%12,2)

But at the same time you also need to check for these two conditions as below for this to work correctly.Below is the sample SQL and probably you can use it as a base to build your expression .. You do not need to declare variables as in SQL and use an "IFF" instead of case

DECLARE @BirthDate DATETIME

DECLARE @CurrentDate DATETIME

SELECT @CurrentDate = '20070210', @BirthDate = '19790519'

SELECT

DATEDIFF(YY, @BirthDate, @CurrentDate) -

CASE

WHEN(

(MONTH(@BirthDate)*100 + DAY(@BirthDate)) >

(MONTH(@CurrentDate)*100 + DAY(@CurrentDate))

) THEN 1

ELSE 0

END

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 2

I used parameters instead of fields, but the following should do exactly what you want. Basically does a divide by 12 with no remainder (removes your need to round) and then uses the mod of the months to give you the accurate remainder. So, for an even number of years, would be like 8.0 and for say 8 and 4 months gives 8.4 etc.

=CStr(DATEDIFF("m", Parameters!start.Value, Parameters!dob.Value) \ 12) & "." & CStr(DATEDIFF("m", Parameters!start.Value, Parameters!dob.Value) Mod 12)

HTH,

Steve.

kathryn.gilbertson Old Hand Points: 312 More actions · Answer 3

Thank you both,

I really appreciate your help - you have saved my sanity.

Thanks

Kat