Question
BlueRose Technologies
AU
Last activity: 17 Mar 2017 12:02 EDT
How to calculate Age from Date of Birth via Function Alias rule
Hi All,
I want to calculate Age from Date of Birth via Function Alias rule where I can fetch the Date of Birth from a table which is in the format '9/20/1946 12:00 AM' so that I can show it as a column in a Report Definition.
I have tried some SQL Server code but is throwing an exception on running the Report Definition.
I have been suggested to make use of the function alias 'pxDifferenceInHours' but not sure how. PFA doc for the design screenshots.
Can somebody help me on this?
Thanks in advance.
Accepted Solution
Pegasystems Inc.
US
Hi Giridhar,
You can try the different ways explained here https://pdn.pega.com/community/pega-product-support/question/want-calculate-age-dob
Regards
Mahesh
Blue Rose Technologies GmbH
BE
Hello Giridhar,
use data transform / declare expression and set the age value as
@(Pega-RULES:ExpressionEvaluators).round(@(Pega-RULES:DateTime).DateTimeDifference(.DateOfBirth+"T000000.000 GMT",@(Pega-RULES:DateTime).CurrentDateTime(), "Y")) +1
Hope this helps.
BlueRose Technologies
AU
Hi AkshaySinha,
How can I use the mentioned expression to one of the columns in my Report Definition?
Thanks in advance.
Accepted Solution
Pegasystems Inc.
US
Hi Giridhar,
You can try the different ways explained here https://pdn.pega.com/community/pega-product-support/question/want-calculate-age-dob
Regards
Mahesh
Religent Systems Pvt Ltd
IN
Simple subtraction of years will not get the right answer always. For example, the difference between Jan 11, 2017 and Mar 23rd, 2014 will result in 3 years but it actually should show 2 years and 10 months. Also, the answer 'difference in years - 1' will as well does not work when 'months in the above example are swapped'. However, if the actual requirement is not interested in months, just showing '2 years' should suffice.
Pegasystems Inc.
US
If I were doing this, I would use the built in Transact-SQL date/time functions (DAY, MONTH, YEAR) in conjunction with an IF...ELSE or CASE construct. For example, if @DOB and @today are your 2 dates and assuming the dates are valid:
DECLARE @birthDay integer, @birthMonth integer, @birthYear integer, @thisDay integer, @thisMonth integer, @thisYear integer, @age integer
SET @birthDay = DAY(@DOB)
SET @birthMonth = MONTH(@DOB)
SET @birthYear = YEAR(@DOB)
SET @thisDay = DAY(@today)
SET @thisMonth = MONTH (@today)
SET @thisYear = YEAR(@today)
IF @thisMonth > @birthMonth SET @age = @thisYear - @birthYear;
ELSE IF @thisMonth = @birthMonth
IF @thisDay >= @birthDay SET @age = @thisYear - @birthYear;
ELSE SET @age = @thisYear - @birthYear - 1;
ELSE SET @age = @thisYear - @birthYear - 1;
BlueRose Technologies
AU
Hi RonLeeGCS,
Thanks for the code snippet. Like AjayKumarYadavJ mentioned earlier in my post, your code snippet will not work to calculate the age in the terms of months and days but it does in years.
However, thanks for the inputs.