Click here to Skip to main content
15,879,239 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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

Then, Duration = EndDate - StartDate

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

I'm trying this expression:
SQL
=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.

What I have tried:

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")
Posted
Updated 3-Aug-21 2:44am
v3

1 solution

You could follow the same pattern that you already have established by removing the previously calculated values from the total, leaving the days.

However, I would probably approach the problem like this..

1. Have a column that calculates the years difference between the dates
2. Another interim column that calculates the start date plus the number of years in 1.
3. Another that calculates the difference in days between the interim column in 2 and the EndDate. Divide that value by 30.5 and convert to an inteer to get the whole months
4. Another column to Add that number of months to the interim date
5. Finally calculate the number of days between the interim date in 4 to the End Date

You can hide the interim columns from the final report, but breaking them out like that makes it a whole lot easier to spot mistakes than your highly complicated formuala.

Alternatively, I did a similar example in SQL on this post How to calculate month and days between two dates[^] which you could convert to a Function instead.

Footnote: the reason for the division by 30.5 is because that is the average number of days in a month and is surprisingly accurate when used over a period which includes or does not include leap years. You could be pedantic and use 30.4 instead. Just remember to truncate to an integer.

Edit: Just in case here is a link on how to hide items in Report Builder Hide an Item (Report Builder) - SQL Server Reporting Services (SSRS) | Microsoft Docs[^]
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900