Calculating Age of Years in SQL

Question

I need some help with SQL Server 2012 trying to calculate an Aging Years Field for a report.

Basically, I have two fields StartDateTime and EndDateTime and here is some sample data below:

StartDateTime                 EndDateTime

 2006-10-10 16:08:13.523      2008-04-11 00:00:00.000
 2016-05-11 13:03:48.093      2016-06-16 00:00:00.000
 2016-08-01 12:44:42.990      2016-08-01 00:00:00.000
 2016-05-20 17:33:27.957      2016-05-25 00:00:00.000
 2006-10-19 21:41:41.350      NULL

I am currently trying to make an AgingYears field to calculate the Aging Years between both fields (as exact as possible.) And when EndDateTime is NULL, it should calculate the difference between GETDATE() and StartDateTime. When StartDateTime is greater than EndDateTime (which I don't know why I have some data like this, but I do), then it should just return back 0.

I tried doing some code based off websites I found related, but it's not helping me much and this is where I'm stuck at:

DATEDIFF(YY, StartDateTime, EndDateTime) - 
    CASE
        WHEN DATEADD(YY,DATEDIFF(YY,StartDateTime, EndDateTime),StartDateTime)
            > A.[END_DTTM] 
        THEN DATEDIFF(YY, StartDateTime, EndDateTime)- 1
        ELSE DATEDIFF(YY, StartDateTime, EndDateTime)
    END AS AgeInYears,

Any help with my code would be greatly appreciated.

Possible duplicate of [How to calculate age (in years) based on Date of Birth and getDate()](http://stackoverflow.com/questions/1572110/how-to-calculate-age-in-years-based-on-date-of-birth-and-getdate) — Jeremy Danyow, Jan 11 '17 at 20:25
@JeremyDanyow It appears that most of the answers on that other question are quite complicated and possibly wrong although I'm sure there are others out there that do have approaches to computing age (regardless of language.) — shawnt00, Jan 11 '17 at 22:07

shawnt00 · Accepted Answer · 2017-01-11T23:26:58.890

Be careful about leap years. How do you want February 29 to February 28 to be treated?

case
    when year(coalesce(EndDateTime, getdate()) > year(StartDateTime)
    then
        datediff(year, StartDateTime, coalesce(EndDateTime, getdate())) -
        case
            when
                EndDateTime is not null and 
                    datepart(month, EndDateTime) < datepart(month, StartDateTime)
                or  datepart(month, EndDateTime) = datepart(month, StartDateTime)
                and datepart(day,   EndDateTime) < datepart(day,   StartDateTime)
            then 1
            when
                EndDateTime is null and
                    datepart(month, getdate())   < datepart(month, StartDateTime)
                or  datepart(month, getdate())   = datepart(month, StartDateTime)
                and datepart(day,   getdate())   < datepart(day,   StartDateTime)
            then 1
            else 0
        end
    else 0
end

I split the EndDateTime and getdate() cases mostly because it looked better without the scroll bar on the long lines.

Here's one way to tweak that logic and catch the leap day condition treating it as a full year even though the dates don't strictly coincide. You'd need to duplicate this expression (substitute getdate() forEndDateTimeeverywhere except theis null` test) in both branches if you keep the "split" logic the way I wrote it above.

            when
                EndDateTime is not null and
                    datepart(month, EndDateTime)     < datepart(month, StartDateTime)
                 or datepart(month, EndDateTime)     = datepart(month, StartDateTime)
                and datepart(day,   EndDateTime)     < datepart(day,   StartDateTime)
                and not -- here's one way to catch the leap day condition
                (
                    -- adding one day this way is deprecated and only works with datetime
                    and datepart(month, StartDateTime + 1) > datepart(month, StartDateTime)
                    and datepart(month, EndDateTime + 1)   > datepart(month, EndDateTime)
                )

http://rextester.com/SBH69843

Btw, once you understand that approach there's a way to condense the same logic using arithmetic on numbers in yyyymmdd format.

(
cast(convert(varchar(8), @EndDateTime, 120) as int) -
cast(convert(varchar(8), @StartDateTime, 120) as int)
) / 10000

All the complicated logic above is simply borrowing from the years place in the subtraction. It's pretty much the same thing.

It's not complicated at all. This is the typical way to sort dates (from different years) first by month and then by day of month. — shawnt00, Jan 11 '17 at 19:22

score 1 · Answer 2 · edited Jan 11 '17 at 23:30

1

I think it should be like this:

SELECT StartDate
    , EndDate
    , CASE 
        WHEN DATEDIFF(YY, StartDate, ISNULL(EndDate, GETDATE())) < 0
            THEN 0
        ELSE DATEDIFF(YY, StartDate, ISNULL(EndDate, GETDATE()))
        END AS AgingYears
FROM YourTableName

edited Jan 11 '17 at 23:30

JotaPardo

817
9
27

answered Jan 11 '17 at 18:52

Mohammed Elshennawy

967
7
15

Calculating Age of Years in SQL

2 Answers2