SSRS experssion to calculate date difference between two dates into years, months and days

Question

Hi, how are you all doing? Hope you are all doing great.

I am looking for a ssrs expression to calculate date duration between two dates into Years, Months and Days.

For example, we have two dates:

StartDate: 3/1/2019

EndDate: 10/5/2021

Duration: EndDate - StartDate

Duration should print ==> 2 Years, 7 Months, 4 days

I'm trying this expression:

=Floor(DateDiff("m",Fields!StartDate.Value,Fields!EndDate.Value)/12).ToString()+" Years, " +(DateDiff("m",Fields!StartDate.Value,Fields!EndDate.Value)-(Floor(DateDiff("m",Fields!StartDate.Value,Fields!EndDate.Value)/12)*12)).ToString()+ " Months"

This expression returns 2 Years, 7 Months

This expression is working fine for Years and months. Anyone who can please help me in extending this expression to calculate remaining days which are 4 in the above example.

Thanks for reading.

Answer 1

I disagree that your calculation is correct. Ignoring completely the issue of leap years your expression only works for certain classes of dates. In this case you have a start date that precedes the end date in days. But if start date has a day that is greater than the end date's day then you get the wrong month. For example changing start date to '3/6/2019' tells you there is 7 months difference when there is only 6 months. The same issue applies to year. If you use start date of '3/6/2020' then it says there is 1 year difference when in fact there is only 7 months. Calculating date differences isn't trivial.

Again, totally ignoring leap years which can be accounted for if you do some careful adjustments, the generally better solution is to first get the difference between the dates in terms of raw days. Then convert the raw days to the equivalent timespan based upon whatever rules you want to follow (e.g. leap year inclusion). As an example your original dates are 949 days apart. Doing rough math where a year has 365 days then that is 2 years. Months are next. If the start day is <= end day then (end month - start month) else (end month - start month - 1). Days are last. If start day <= end day then (end day - start day) else it gets harder. What is the difference between '3/6 and '4/5? Given that this is under a month by a day I'd argue that it is (31-6) + 5 = 31. (Might be off by one here, check the math against samples). If it were '3/31' and '4/1' then that would be 1 day difference (31 - 31) + 1.

Given all this (in T-SQL but can be mostly converted to SSRS expression with minor changes):

DECLARE @days INT = DateDiff(day, @startDate, @endDate)
DECLARE @startDay INT = Day(@startDate)
DECLARE @endDay INT = Day(@endDate)
DECLARE @lastDayOfMonth DATE = DateAdd(day, -1, DateAdd(month, DateDiff(month, 0, @startDate) + 1, 0))
SELECT @days / 365,
       IIF (@startDay <= @endDay, Month(@endDate) - Month(@startDate), Month(@endDate) - Month(@startDate) - 1),
       IIF (@startDay <= @endDay, @endDay - @startDay, (DAY(@lastDayOfMonth) - @startDay) + @endDay)

Note that I created temp variables to eliminate replicated expressions. You should probably consider adding calculated columns to your query to calculate this as well to speed things up and make it a little easier to use. I would even go so far as to include the month diff in the query but I left it here so it made more sense.

Going back to my other test scenarios (3/6/2019 to 10/5/2021 -> 2 years, 6 months, 30 days), (3/6/2020 to 10/5/2021 -> 1 year, 6 months, 30 days)

Answer 2

Hi @Muhammad Hussnain Javed ,
If you are willing to try TSQL, I think the problem will become much simpler:

Declare @StartDate datetime   
Declare @EndDate datetime    
Declare @years varchar(40)    
Declare @months varchar(30)    
Declare @days varchar(30)    
set @StartDate='2019-03-01'--StartDate    
set @EndDate ='2021-10-05'--EndDate    
select @years=datediff(year,@StartDate,@EndDate)-- To find Years    
select @months=datediff(month,@StartDate,@EndDate)-(datediff(year,@StartDate,@EndDate)*12) -- To Find Months    
select @days=datepart(d,@EndDate)-datepart(d,@StartDate)-- To Find Days    
select @years  +' years,   ' +@months +' months,   '+@days   +' days' asYearMonthDay  

Output：

For more information，please refer to: How to use DATEDIFF to return year, month and day?
Best Regards,
Joy

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.