Расчет возраста лет в SQL

Question

Мне нужна некоторая помощь с SQL Server 2012, пытающимся вычислить поле для возраста для отчета.

По сути, у меня есть два поля StartDateTime и EndDateTime, и вот несколько примеров данных ниже:

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

В настоящее время я пытаюсь создать поле AgingYears, чтобы рассчитать годы старения между обоими полями (как можно точнее). И когда EndDateTime имеет значение NULL, он должен вычислять разницу между GETDATE() и StartDateTime. Когда StartDateTime больше EndDateTime (что я не знаю, почему у меня есть такие данные, но я делаю), тогда он должен просто вернуть обратно 0.

Я попытался создать некоторый код, основанный на веб-сайтах, которые я нашел связанными, но это не очень мне помогает, и вот где я застрял:

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,

Любая помощь с моим кодом будет принята с благодарностью.

Answer 1

Будьте осторожны с високосными годами. Как вы хотите, чтобы лечили с 29 по 28 февраля?

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

Я разделил EndDateTime а также getdate() в основном потому, что выглядело лучше без полосы прокрутки на длинных строках.

Вот один из способов изменить эту логику и поймать условие високосного дня, рассматривая его как полный год, даже если даты не совпадают строго. Вам нужно будет продублировать это выражение (заменить getdate() forEndDateTimeeverywhere except theis null` test) в обеих ветках, если вы придерживаетесь логики "split", как я писал выше.

            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

Кстати, как только вы поймете этот подход, вы сможете уплотнить ту же логику, используя арифметику чисел в формате ггггммдд.

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

Вся сложная логика выше просто заимствует место лет в вычитании. Это почти то же самое.

Answer 2

Я думаю, что это должно быть так:

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