Posted: 18 Jan 2017 3:03 EST Last activity: 17 Mar 2017 12:02 EDT
How to calculate Age from Date of Birth via Function Alias rule
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.
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.
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: