how to calculate time difference between two columns

Question

• in my report query, i have two columns received date and closed date. how do you calculate the tiem difference between received date and closed date.

similarly i have received time column and a closed time column , i want to calculate the time difference in  hh/mm/ss

can someone help me ?

Friday, November 11, 2011 3:19 AM

• Hi msmustard,

Based on your requirement, I think that we can use the DateDiff() function to achieve this.
For example, we can use the following expression to calculate the time difference between “10:00:00” and “11:11:11”, and the result display with “hh:mm:ss” format:
=Datediff(DateInterval.Hour,#10:00:00#,#11:11:11#) &":" & Datediff(DateInterval.Minute,#10:00:00#,#11:11:11#) MOD 60 & ":" & (Datediff(DateInterval.Second,#10:00:00#,#11:11:11#) MOD 60) MOD 60
The result like this: 1:11:11

http://technet.microsoft.com/en-us/library/aa337092(v=sql.100).aspx

Thanks,
Bin Long

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Monday, November 14, 2011 6:13 AM

All replies

• You can do it in two ways.

• Assuming you are working against a SQL Server data source, add a derived column to the select clause using the DATEDIFF function. Your query will look something like below:
select
datediff(hh,Rec_Dt,Closed_Dt) as TimeDif
from
<tablename>

• Or add calculated fields to the data set in SSRS, you can use the Datediff function from the available data & time functions. Ans use the calculated fields in the data region.

Friday, November 11, 2011 4:40 AM
• Friday, November 11, 2011 4:48 AM
• Hi msmustard,

Based on your requirement, I think that we can use the DateDiff() function to achieve this.
For example, we can use the following expression to calculate the time difference between “10:00:00” and “11:11:11”, and the result display with “hh:mm:ss” format:
=Datediff(DateInterval.Hour,#10:00:00#,#11:11:11#) &":" & Datediff(DateInterval.Minute,#10:00:00#,#11:11:11#) MOD 60 & ":" & (Datediff(DateInterval.Second,#10:00:00#,#11:11:11#) MOD 60) MOD 60
The result like this: 1:11:11