Calculate Exact Date Difference in Years Using SQL

Calculate exact date difference in years using SQL

Have you tried getting the difference in months instead and then calculating the years that way? For example 30 months / 12 would be 2.5 years.

Edit: This SQL query contains several approaches to calculate the date difference:

SELECT CONVERT(date, GetDate() - 912) AS calcDate
      ,DATEDIFF(DAY, GetDate() - 912, GetDate()) diffDays
      ,DATEDIFF(DAY, GetDate() - 912, GetDate()) / 365.0 diffDaysCalc
      ,DATEDIFF(MONTH, GetDate() - 912, GetDate()) diffMonths
      ,DATEDIFF(MONTH, GetDate() - 912, GetDate()) / 12.0 diffMonthsCalc
      ,DATEDIFF(YEAR, GetDate() - 912, GetDate()) diffYears

How to get the exact year difference in SQL

You can take the year difference and then subtract 1 if necessary:

select (datediff(year, val1, val2) -
        (case when month(val1) < month(val2) then 0
              when month(val1) > month(val2) then 1
              when day(val1) <= day(val2) then 0
              else 1
         end)
        ) as diff_years

How to calculate age (in years) based on Date of Birth and getDate()

There are issues with leap year/days and the following method, see the update below:

try this:

DECLARE @dob  datetime
SET @dob='1992-01-09 00:00:00'

SELECT DATEDIFF(hour,@dob,GETDATE())/8766.0 AS AgeYearsDecimal
    ,CONVERT(int,ROUND(DATEDIFF(hour,@dob,GETDATE())/8766.0,0)) AS AgeYearsIntRound
    ,DATEDIFF(hour,@dob,GETDATE())/8766 AS AgeYearsIntTrunc

OUTPUT:

AgeYearsDecimal                         AgeYearsIntRound AgeYearsIntTrunc
--------------------------------------- ---------------- ----------------
17.767054                               18               17

(1 row(s) affected)

UPDATE here are some more accurate methods:

BEST METHOD FOR YEARS IN INT

DECLARE @Now  datetime, @Dob datetime
SELECT   @Now='1990-05-05', @Dob='1980-05-05'  --results in 10
--SELECT @Now='1990-05-04', @Dob='1980-05-05'  --results in  9
--SELECT @Now='1989-05-06', @Dob='1980-05-05'  --results in  9
--SELECT @Now='1990-05-06', @Dob='1980-05-05'  --results in 10
--SELECT @Now='1990-12-06', @Dob='1980-05-05'  --results in 10
--SELECT @Now='1991-05-04', @Dob='1980-05-05'  --results in 10

SELECT
    (CONVERT(int,CONVERT(char(8),@Now,112))-CONVERT(char(8),@Dob,112))/10000 AS AgeIntYears

you can change the above 10000 to 10000.0 and get decimals, but it will not be as accurate as the method below.

BEST METHOD FOR YEARS IN DECIMAL

DECLARE @Now  datetime, @Dob datetime
SELECT   @Now='1990-05-05', @Dob='1980-05-05' --results in 10.000000000000
--SELECT @Now='1990-05-04', @Dob='1980-05-05' --results in  9.997260273973
--SELECT @Now='1989-05-06', @Dob='1980-05-05' --results in  9.002739726027
--SELECT @Now='1990-05-06', @Dob='1980-05-05' --results in 10.002739726027
--SELECT @Now='1990-12-06', @Dob='1980-05-05' --results in 10.589041095890
--SELECT @Now='1991-05-04', @Dob='1980-05-05' --results in 10.997260273973

SELECT 1.0* DateDiff(yy,@Dob,@Now) 
    +CASE 
         WHEN @Now >= DATEFROMPARTS(DATEPART(yyyy,@Now),DATEPART(m,@Dob),DATEPART(d,@Dob)) THEN  --birthday has happened for the @now year, so add some portion onto the year difference
           (  1.0   --force automatic conversions from int to decimal
              * DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),DATEPART(m,@Dob),DATEPART(d,@Dob)),@Now) --number of days difference between the @Now year birthday and the @Now day
              / DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),1,1),DATEFROMPARTS(DATEPART(yyyy,@Now)+1,1,1)) --number of days in the @Now year
           )
         ELSE  --birthday has not been reached for the last year, so remove some portion of the year difference
           -1 --remove this fractional difference onto the age
           * (  -1.0   --force automatic conversions from int to decimal
                * DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),DATEPART(m,@Dob),DATEPART(d,@Dob)),@Now) --number of days difference between the @Now year birthday and the @Now day
                / DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),1,1),DATEFROMPARTS(DATEPART(yyyy,@Now)+1,1,1)) --number of days in the @Now year
             )
     END AS AgeYearsDecimal

How to get difference of days/months/years (datediff) between two dates?

SELECT
  AGE('2012-03-05', '2010-04-01'),
  DATE_PART('year', AGE('2012-03-05', '2010-04-01')) AS years,
  DATE_PART('month', AGE('2012-03-05', '2010-04-01')) AS months,
  DATE_PART('day', AGE('2012-03-05', '2010-04-01')) AS days;

This will give you full years, month, days ... between two dates:

          age          | years | months | days
-----------------------+-------+--------+------
 1 year 11 mons 4 days |     1 |     11 |    4

More detailed datediff information.

How to calculate The duration/age in year, month, days between two dates in SQL? instituitiondate and nextdate has date data type?

I don't know if there is an exact answer to your question, but this should give a very good approximation:

SELECT
    instituitiondate, 
    MAX(NextDate) AS LastDate,
    floor(datediff(day, InstituitionDate, MAX(NextDate)) / 365.25) as Years, 
    floor(datediff(day, InstituitionDate, MAX(NextDate)) / 30.4375) % 12 as Months, 
    floor(datediff(day, InstituitionDate, MAX(NextDate)) % 30.4375) as Days 
FROM
    Main_Cause_List 
WHERE
    CaseNo = '372/4/2020' 
GROUP BY
    InstituitionDate;

It calculates the age considering an average of 365.25 days per year, and 30.44 days per month.

Edited: changed the days per month from from 30.44 to 30.4375 because 30.4375 * 12 = 365.25, removing the very small inconsistency on the way the years are calculated versus their correspondent months and days left.

Get date difference in year, month, and days SQL

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,

Calculating the number of dates in specific year, between two dates

You didn't specify your dbms, but in general terms:

• Get the 1st day of the @EndDate year i.e. January 1, 2017
• Then calculate the days difference between @FirstOfYear and @EndDate

For example in MySQL, you could use MAKEDATE() to get the first of the year. Then DATEDIFF() to calculate the number of days

SET @StartDate = '2016-12-30';
SET @EndDate = '2017-01-05';  

SELECT DateDiff(@endDate, MAKEDATE(Year(@endDate),1)) + 1 AS DaysInEndDateYear

Result:

| DaysDiffValue|
| ----------------: |
|                 5 |

If you also need to handle ranges where both @StartDate and @EndDate are in the same year:

SET @StartDate = '2017-01-05';
SET @EndDate = '2017-03-14';  

SELECT CASE WHEN Year(@StartDate) = Year(@EndDate) THEN DateDiff(@EndDate, @StartDate) + 1
            ELSE DateDiff(@endDate, MAKEDATE(Year(@endDate),1)) + 1
       END AS DaysInEndDateYear

Results:

| DaysInEndDateYear |
| ----------------: |
|                69 |

db<>fiddle here

How to Calculate datediff between two date in year, month & days?

Not sure if it's 100% correct, but something like this:

declare @d1 datetime, @d2 datetime, @y int, @m int, @d int
set @d1 = '2009-09-23'
set @d2 = '2012-04-23'

set @y = datediff(year, @d1, @d2) - 1
set @m = datediff(month, dateadd(year, @y, @d1), @d2) 
if dateadd(month, @m, dateadd(year, @y, @d1)) > @d2 set @m = @m - 1

set @d = datediff(day, dateadd(month, @m, dateadd(year, @y, @d1)), @d2)
print cast(@y as nvarchar) + ' year(s) ' + cast(@m as nvarchar) + ' month(s) and ' + cast(@d as nvarchar) + ' day(s)'

---

Related Topics