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

1 Reputation point
2021-08-04T13:32:22.51+00:00

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.

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,827 questions

1. 49,251 Reputation points
2021-08-04T15:45:13.9+00:00

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)

2. 15,566 Reputation points
2021-08-05T02:50:28.733+00:00

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：