CODEWITHSUNDEEP
sqlsql-server-2012casedatediffgetdate
smul86
smul86

Reputation: 411

Calculating Age of Years in SQL

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.

Upvotes: 3

Views: 327

Answers (2)

Mohammed Elshennawy
Mohammed Elshennawy

Reputation: 967

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

Upvotes: 1

shawnt00
shawnt00

Reputation: 17915

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.

Upvotes: 2

Related Questions