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
CREATE FUNCTION [dbo].[FindDateDiff](@Date1 date,@Date2 date, @IncludeTheEnDate bit)
RETURNS TABLE
AS
RETURN
(
SELECT
CALC.Years,CALC.Months,D.Days,
Duration = RTRIM(Case When CALC.Years > 0 Then CONCAT(CALC.Years, ' year(s) ') Else '' End
+ Case When CALC.Months > 0 Then CONCAT(CALC.Months, ' month(s) ') Else '' End
+ Case When D.Days > 0 OR (CALC.Years=0 AND CALC.Months=0) Then CONCAT(D.Days, ' day(s)') Else '' End)
FROM (VALUES(IIF(@Date1<@Date2,@Date1,@Date2),DATEADD(DAY, IIF(@IncludeTheEnDate=0,0,1), IIF(@Date1<@Date2,@Date2,@Date1)))) T(StartDate, EndDate)
CROSS APPLY(Select
TempEndYear = Case When ISDATE(CONCAT(YEAR(T.EndDate), FORMAT(T.StartDate,'-MM-dd')))=1 Then CONCAT(YEAR(T.EndDate), FORMAT(T.StartDate,'-MM-dd'))
Else CONCAT(YEAR(T.EndDate),'-02-28') End
) TEY
CROSS APPLY(Select EndYear = Case When TEY.TempEndYear > T.EndDate Then DATEADD(YEAR, -1, TEY.TempEndYear) Else TEY.TempEndYear End) EY
CROSS APPLY(Select
Years = DATEDIFF(YEAR,T.StartDate,EY.EndYear),
Months = DATEDIFF(MONTH,EY.EndYear,T.EndDate)-IIF(DAY(EY.EndYear)>DAY(T.EndDate),1,0)
) CALC
CROSS APPLY(Select Days = DATEDIFF(DAY,DATEADD(MONTH,CALC.Months,DATEADD(YEAR,CALC.Years,T.StartDate)),T.EndDate)) D
)
Sample:
Select [From] = '2021-01-01',[To] = '2021-12-31',IncludeEndDate='Yes',* From dbo.FindDateDiff('2021-01-01','2021-12-31',1)
Select [From] = '2021-01-01',[To] = '2021-12-31',IncludeEndDate='No',* From dbo.FindDateDiff('2021-01-01','2021-12-31',0)
Select [From] = '2015-12-15',[To] = '2018-12-14',IncludeEndDate='Yes',* From dbo.FindDateDiff('2015-12-15','2018-12-14',1)
Select [From] = '2015-12-15',[To] = '2018-12-14',IncludeEndDate='No',* From dbo.FindDateDiff('2015-12-15','2018-12-14',0)
