Is there a way to calculate how old someone is based on today's date and their birthday then display it in following manners:
If a user is less than (<) 1 year old THEN show their age in MM & days.
Example: 10 months & 2 days old
If a user is more than 1 year old AND less than 6 years old THEN show their age in YY & MM & days.
Example: 5 years & 3 months & 10 days old
If a user is more than 6 years old THEN display their age in YY.
Example: 12 years
Probably not the most efficient way to go about it, but here's how I did it:
I had to first get the date difference between today's date and person's birthdate. I used it to get years, months, days, etc by combining it with ABS(), and Remainder (%) function.
declare @year int = 365
declare @month int = 30
declare @sixYears int = 2190
select
--CAST(DATEDIFF(mm, a.BirthDateTime, getdate()) AS VARCHAR) as GetMonth,
--CAST(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, a.BirthDateTime, getdate()), a.BirthDateTime), getdate()) AS VARCHAR) as GetDays,
CASE
WHEN
DATEDIFF(dd,a.BirthDateTime,getdate()) < @year
THEN
cast((DATEDIFF(dd,a.BirthDateTime,getdate()) / (@month)) as varchar) +' Months & ' +
CAST(ABS(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, a.BirthDateTime, getdate()), a.BirthDateTime), getdate())) AS VARCHAR)
+ ' Days'
WHEN
DATEDIFF(dd,a.BirthDateTime,getdate()) between @year and @sixYears
THEN
cast((DATEDIFF(dd,a.BirthDateTime,getdate()) / (@year)) as varchar) +' Years & ' +
CAST((DATEDIFF(mm, a.BirthDateTime, getdate()) % (12)) AS VARCHAR) + ' Months'
WHEN DATEDIFF(dd,a.BirthDateTime,getdate()) > @sixYears
THEN cast(a.Age as varchar) + ' Years'
end as FinalAGE,