Calculation DateDiff with date and integer

Question

I want calculate and then count the difference in hours between two dates with hours if they are bigger than 72.

In the database the dates and hours are seperated in two columns. So the database gives me the following:

Column	Type
StartDate	date (dd.mm.yyyy)
StartHour	integer
EndDate	date (dd.mm.yyyy)
EndHour	integer

It should work like this:

• Calculate the difference between two dates in days
• Multiply the result by 24 to get hours
• Add the difference in hours
• check if the result is bigger than 72
• if yes, count it. if no, do not count it.

In Report Builder I tired the following expression:

=SUM(
    IIF(
        (DateDiff("dd",Fields!StartDate.Value,Fields!EndDate.Value) * 24 + Fields!StartHour.Value - Fields!EndHour.Value
    )
    > 72, 1, 0
        )
    )

This expressions does not work, it returns #error. So I thought, that I need to convert the DateDiff to Integer with CInt, but according to Microsoft documentation DateDiff already returns an integer.

Another problem is that simply subtracting the hourly values may not produce the desired result. However, I do not know how to solve this.

Thanks in advance.

Answer 1

Verify that your datediff for the dates works with the following expression

=DATEDIFF("d",Fields!StartDate.Value,Fields!EndDate.Value)

If not, try converting the date columns to proper date format.

Work every expression in separate column to verify everything works before combining them.