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

select
    case when ltrim(rtrim(cast(floor(datediff(n,StartTime,EndTime)/60) as char(3)))) = 0 then '00'
    else ltrim(rtrim(cast(floor(datediff(n,StartTime,EndTime)/60) as char(3)))) end + ':' +        
    case when ltrim(rtrim(cast(datediff(n,StartTime,EndTime) % 60 as char(3)))) = 0 then '00'
    else ltrim(rtrim(cast(datediff(n,StartTime,EndTime) % 60 as char(3)))) end As TotalHrs
    from 
 tblEmployee
where id = 5
 
 

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.

[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