question

MuhammadHussnainJaved-1830 avatar image
0 Votes"
MuhammadHussnainJaved-1830 asked cooldadtx commented

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

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.


sql-server-reporting-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cooldadtx avatar image
0 Votes"
cooldadtx answered

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)

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Joyzhao-MSFT avatar image
0 Votes"
Joyzhao-MSFT answered cooldadtx commented

Hi @MuhammadHussnainJaved-1830 ,
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:
120618-04.jpg

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.



04.jpg (7.8 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Regarding the use of Function in SSRS, I found relevant information, Hope it helps.
"Calculating Age Between Two Dates (Years,Months And Days)"


0 Votes 0 ·

Same Problem in my Crystral Report! I solved This in ms access but Seagate crystral report dose not supprt the custom function!

Can you help me please?

0 Votes 0 ·

Please create your own post as it is unrelated to this question about SSRS.

0 Votes 0 ·