Get date difference in year, month, and days SQL

Question

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

Please explain what happens when someone is born at the end of a month. Say someone is born on March 31, what is their age on April 30th, May1st, and so on? — Gordon Linoff, Mar 22 '16 at 22:19
*Scratching my head*..How about also displaying days after the month. This way no reason to figure out which month to use. I'll update my question. — NonProgrammer, Mar 22 '16 at 22:35
Also, you mentioned that you want the format MM & days. Does that mean "05 & 02" or do you really want it written out as shown in the examples and skipping the leading 0? — Ralph, Mar 22 '16 at 22:37
I'd calculate the difference between two dates in days (which is easy and non-ambiguous) and then say that a year has 365.2425 days and a month has 30.436875 days and then display the approximate age of the user. — Vladimir Baranov, Mar 23 '16 at 04:57
This kind of logic is better implemented in the application tier, outside of the scope of SQL code. That would be my recommendation to you. — EduardoCMB, Mar 23 '16 at 13:46
@VladimirBaranov your advise worked like a charm. Unfortunately, I didn't use the exact days, but the result is still acceptable. — NonProgrammer, Mar 24 '16 at 18:56

score 1 · Answer 1 · edited Mar 22 '16 at 23:22

1

This is basically what you are looking for:

DECLARE @date1 DATETIME
     , @date2 DATETIME;

SELECT @date1 = '1/1/2008'
    , @date2 = GETDATE();
SELECT CASE
         WHEN DATEDIFF(YEAR, @date1, @date2) < 1 THEN CAST(DATEDIFF(mm, @date1, @date2) AS VARCHAR)+' Months & '+CAST(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, @date1, @date2), @date1), @date2) AS VARCHAR)+' Days'
         WHEN DATEDIFF(YEAR, @date1, @date2) BETWEEN 1 AND 5 THEN CAST(DATEDIFF(mm, @date1, @date2) / 12 AS VARCHAR)+' Years & '+CAST(DATEDIFF(mm, @date1, @date2) % 12 AS VARCHAR)+' Months'
         WHEN DATEDIFF(YEAR, @date1, @date2) >= 6 THEN CAST(DATEDIFF(YEAR, @date1, @date2) AS VARCHAR)+' Years'
      END;

Result for when a user is less than (<) 1 year old THEN show their age in MM & days:

Result for when a user is more than 1 year old AND less than 6 years old THEN show their age in YY & MM & days:

Result for when a user is more than 6 years old THEN display their age in YY:

edited Mar 22 '16 at 23:22

sqluser

5,502
7
36
50

answered Mar 22 '16 at 22:39

Fuzzy

3,810
2
15
33

1

Try this as @date1 `1/1/2010`. Updated your answer. – sqluser Mar 22 '16 at 23:19
Also try `02-27-2016` – sqluser Mar 22 '16 at 23:28
@sqluser Thank you for that. too late in the day. – Fuzzy Mar 23 '16 at 00:41
1

If you choose @date1 = 1/28/2016, it gives you 2 months & -4 days. Overall, works great! – NonProgrammer Mar 24 '16 at 15:44
1

Also, if you use @date1 = '5/25/2015' , it gives you 0 Years & 10 Months instead of MM & days format. – NonProgrammer Mar 24 '16 at 16:40
@NonProgrammer thanks for letting me know I'll have a look at it over the long weekend and see why it's doing that. – Fuzzy Mar 24 '16 at 21:00

score 1 · Answer 2 · edited May 23 '17 at 12:15

from this previous question How to calculate age in T-SQL with years, months, and days

you can do procedure like this

       CREATE procedure [dbo].[proc_datediff]
       (
        @date datetime
        )
       as
      begin 
  DECLARE @diff varchar(70)
  DECLARE  @tmpdate datetime, @years int, @months int, @days int

 SELECT @tmpdate = @date

    SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN  
    (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = 
   MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END
   SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
  SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN 
 DAY(@date) > DAY(GETDATE()) THEN 1 ELSE 0 END
   SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
 SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())
  select @diff=
  case
     when @years < 1 then
   concat( @months,'  Months ',@days,'  days ' )
   when @years >=1 and @years < 6
    then 
    concat(@years,'  year ', @months,'  Months ',@days,'  days ' )
 when @years >= 6  then

  concat( @years,'  years ' )
   end;
 select @diff

 end
 execute proc_datediff '1/1/2016'
  go

Due to an older version, I am not able to use concat so I can't verify your query, but gave you a bump up. — NonProgrammer, Mar 24 '16 at 18:51

score 1 · Answer 3 · answered Jul 22 '21 at 06:46

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)

this is great solution – Chanuka Naveen Koswatta Apr 13 '22 at 04:54 — Chanuka Naveen Koswatta, Apr 13 '22 at 04:54

score 0 · Accepted Answer · answered Mar 24 '16 at 18:53

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,

score 0 · Answer 5 · edited Oct 10 '19 at 13:22

0

dc.DayMarker = cast(getdate() as date)

DAYmarker is your date field.

edited Oct 10 '19 at 13:22

senthilkumar2185

2,536
3
22
36

answered Oct 10 '19 at 13:19

BenM

1

Get date difference in year, month, and days SQL

5 Answers5