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(dat etime,cast(((DA TEDIFF(minute, StartTime, EndTime) - cast(BreakPerio d as int)) / 60) as char(5)) + ':' + cast(((DATEDIFF (minute, StartTime, EndTime) - cast(BreakPerio d as int)) % 60) as char(5))),108) as TotalCalculated Hours
from tblEmployee