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

Question

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 3-Aug-21 1:33am

M Hussnain Javed

Updated 3-Aug-21 2:44am

CHill60

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Answer 1 · 2021-08-03T02:44:00

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[^]