How to get the difference of dates or TAT in ssrs

Question

I want to get the Turn Around Time(TAT) of dates. for example: I have a createddatetime 2016-11-02 06:21:34.000 and endeddatetime 2016-11-02 22:00:00.000. I want to get the difference of two dates by 2 decimal places. Anyone can help for the SSRS expression needed to perform this? Result for the above diff is 0.67

TIA!

Answer 1

With number formatting set to Number with 2 decimal places, this expression gives you the same result as your SQL statement:

=Round(DateDiff(DateInterval.Second,Fields!FirstDate.Value,Fields!SecondDate.Value) /60 / 60, 0) / 24

You need to calculate the number of seconds and round this as an hour value to emulate the behaviour of SQL's DATEDIFF function as the .NET version only considers complete units when comparing dates. See this question for further details of this: SQL Server DateDiff Vs .Net DateDiff.

If you just used:

=DateDiff(DateInterval.Day,Fields!FirstDate.Value,Fields!SecondDate.Value)

then you'll get a result of 0.00 as there are no complete days between these dates, rather than the 0.65 you get from the SQL statement.

Similarly, if you try:

=DateDiff(DateInterval.Hour,Fields!FirstDate.Value,Fields!SecondDate.Value) / 24

you'll get a result of 0.63, as the number of complete hours between your dates is 15 and 15 / 24 = 0.625.