subtract two datetime values

dha lak

Hi,

I am searching for a way to find the total no of hours:min between two dates.

My table design is as below:

EmployeeId | StartTime | EndTime | BreakPeriod |

Sample data:
100 | 2011-09-14 08:00:00.000 | 2011-09-14 15:30:00.000 | 30

I need to find the total no of hours:mm between these two datetime values. (excluding the breakperiod).

-----

I tried the following query. But it includes the BreakPeriod

I want to find solution for both scenarios. That is when (i) the breakperiod is in minutes for eg: 100 minutes and also (ii) when the breakperiod is in hh:mm eg: 1:45


Can anyone please help me with this problem. This is very urgent.

Jan 28 '12 #1

dha lak

I solved using this query.

SELECT
convert(varchar(5),convert(datetime,cast(((DATEDIF F(minute, StartTime, EndTime) - cast(BreakPeriod as int)) / 60) as char(5)) + ':' + cast(((DATEDIFF(minute, StartTime, EndTime) - cast(BreakPeriod as int)) % 60) as char(5))),108) as TotalCalculatedHours
from tblEmployee

Jan 30 '12 #2