Geek asks and answers

Difference in date (in years) incl. summer fraction

Is there an easy way to calculate the difference between two dates that are:

  • expresses this as the number of years, including. annual share; and
  • takes leap years into account?

For example, the difference between March 1, 2011 and March 1, 2012 is 1 year. However, if I use DATEDIFF (day, .., ..) and dividing by 365, I get a (unwanted) answer of 1.00274 due to a leap year.

To be clear, I also need an annual share (i.e. not just an integer number of years). For example, the difference between March 1, 2011 and March 3, 2012 is 1.005479 (1 year + 2/365 years). The difference between March 1, 2011 and February 29, 2012 is 0.997268 (0 year + 365/366 years).

So, in general, the conclusion (in DECIMAL (7.6)) from the above two examples will be:

1.000000 1.005479 0.997268

+6
source share
5 answers

Here is the request. But in your example, a logical error. I think that "the difference between March 1, 2011 and March 3, 2012 is 1.005479 (1 year + 2/365 years)" should be (1 year + 2/366 years), since 02/29/2012 between March 3, 2011 and March 3, 2012, so last year 366 days.

    Declare @BDate datetime
    Declare @EDate datetime
    SET @BDate='2011-03-01'
    SET @EDate='2012-02-29'

    select 

    datediff(year,@BDate,@Edate)- 
    case when dateadd(year,datediff(year,@BDate,@Edate),@BDate)>@Edate then 1 else 0 end 
    +cast(datediff(day,dateadd(year,datediff(year,@BDate,@Edate)-
    case when dateadd(year,datediff(year,@BDate,@Edate),@BDate)>@Edate then 1 else 0 end   ,@BDate),@Edate) as float)/
cast(datediff(day,dateadd(year,-1,@Edate),@Edate) as float)
+4
source

I think this works (I hope you have selected names that you can use):

declare @StartTime datetime
declare @EndTime datetime

select @StartTime = '20110301',@EndTime = '20120303'

select YearsDiffNorm + ((DaysIntoYear * 1.0) / (DaysIntoYear + DaysRemainingInYear))
from (
select
    YearsDiffNorm,
    DATEDIFF(day,DATEADD(YEAR,YearsDiffNorm,@StartTime),@EndTime) as DaysIntoYear,
    DATEDIFF(day,@EndTime,DATEADD(YEAR,YearsDiffNorm+1,@StartTime)) as DaysRemainingInYear
from (
select CASE WHEN DATEADD(year,YearsDiff,@StartTime) > @EndTime then YearsDiff - 1 else YearsDiff END as YearsDiffNorm
from (
    select DATEDIFF(year,@StartTime,@EndTime) as YearsDiff
) t
) t2
) t3
+3
source

Try it,

SELECT Cast(DateDiff(yyyy, '2011-03-01', '2012-03-01') As VARCHAR) + 'Yer : '
+ Cast(DateDiff(mm, '2011-03-01', '2012-03-01') As VARCHAR) + 'Mon : '
+ Cast(DateDiff(dd, '2011-03-01', '2012-03-01') As VARCHAR) + 'Dte'

Hope this helps you, thanks.

0
source

try the following:

   DECLARE @stdate datetime,@eddate datetime
    SET @stdate='2007-02-01'
    SET @eddate='2012-03-03'

    ;WITH CTE as (
    select DATEDIFF(yy,@stdate,convert(datetime,cast(DATEPART(year,@eddate) as varchar)+'-'+cast(DATEPART(month,@stdate) as varchar)+'-'+cast(DATEPART(dd,@stdate) as varchar))) yrs,
    DATEDIFF(dd,convert(datetime,cast(DATEPART(year,@eddate) as varchar)+'-'+cast(DATEPART(month,@stdate) as varchar)+'-'+cast(DATEPART(dd,@stdate) as varchar)),@eddate) as dayss,
    CAST(CASE WHEN DATEPART(dd,DATEADD(mm,datediff(mm,-1,(convert(datetime,cast(DATEPART(year,@eddate) as varchar)+'-'+cast(DATEPART(month,@stdate) as varchar)+'-'+cast(DATEPART(dd,@stdate) as varchar)))),-1)) = 29 then 366 else 365 end as float) as ydays
    )
    select yrs+dayss/cast(ydays as float) from CTE
0
source 
DECLARE @FromDate DATETIME = '2019-02-28', @ToDate DATETIME = '2020-02-26',
@diff_year_temp int, @diff_year int,@diff_year_frac float,@ToDateDays float,@FromDateDays float,@y_to int,@y_from int,@diff float,
@FromDec float,@JanTo float,@JanToFrac float,@FromDecFrac float,@ToDateplusone datetime,@FromplusToyear datetime

set @diff_year_temp=datediff(year,@FromDate,@ToDate)
set @FromplusToyear=case when DATEPART(day,@FromDate)=29 and datepart(month,@FromDate)=2 then dateadd(day,1,dateadd(year,@diff_year_temp,@FromDate)) else  dateadd(year,@diff_year_temp,@FromDate) end
set @ToDateplusone=dateadd(day,1,@ToDate)
set @y_to = datepart(year,@ToDate)
set @y_from=datepart(year,@FromDate)
set @FromDec=datediff(day,@FromDate,cast(CONCAT(@y_from,'-12-31') as date))+1
set @JanTo=datediff(day,cast(CONCAT(@y_to,'-01-01') as date),@ToDate)+1
set @ToDateDays = DATEDIFF(day,  cast(@y_to as char(4)),  cast(@y_to+1 as char(4))) 
set @FromDateDays = DATEDIFF(day,  cast(@y_from as char(4)),  cast(@y_from+1 as char(4)))

set @FromDecFrac=@FromDec/@FromDateDays
set @JanToFrac=@JanTo/@ToDateDays
set @diff_year=case when @ToDateplusone >= @FromplusToyear  then @diff_year_temp else @diff_year_temp-1 end
set @diff_year_frac=case when @ToDateplusone >= @FromplusToyear then datediff(day,@FromplusToyear,@ToDateplusone)/@ToDateDays  else  @JanToFrac+@FromDecFrac end
set @diff=@diff_year+@diff_year_frac

select @diff
0
source

More articles:


All Articles