problem with FMS, Hour Difference

44 Views

Hi Experts,

i am having problems with my FMS, i think this is due subtracting 15 mins from the Time..

here is the Sample formula :

(hour2 - hour 1 ) - 15 mins = result

---

(16:00 - 12:45 )- 15 mins=m 3 hrs 0 mins - this is correct

but if the value is (16:00 - 12 : 46) - 15 mins = 3 hrs 1 min(s), this is wrong. because it should be 2 hrs 59 min(s)

-

here is my code..

Declare @date1 as datetime

Declare @date2 as datetime

Declare @hour1 as datetime

Declare @hour2 as datetime

declare @datehour1 as datetime

declare @datehour2 as datetime

Declare @hrsDiff as int

Declare @minDiff as int

Declare @result as varchar(20)

declare @Replace as varchar(10)

Set @date1 = '2011/08/08'

Set @date2 = '2011/08/08'

Set @hour1 = '12:45'

Set @hour2 = '16:00'

--set @hour2 =DATEADD (hh,-1,@hour2)

set @hour2 =DATEADD (MI,-15,@hour2)

set @datehour1 = @date1 + @hour1

set @datehour2 = @date2 + @hour2

Set @hrsDiff = DATEDIFF(hh, @datehour1, @datehour2)

--select @hrsDiff

Set @minDiff = DATEDIFF(mi,DATEADD(hh,DATEDIFF(hh, @datehour1, @datehour2),@datehour1),@datehour2)

set @Replace = REPLACE (@minDiff,'-',' ' )

Set @result = CONVERT(VARCHAR(10),@hrsDiff ) + ' hrs ' + CONVERT(VARCHAR(10),@Replace ) + ' Min(s) '

Select @result

--Select CONVERT(VARCHAR(5), DATEADD(mi,-15,CONVERT(datetime,@result)) , 108)

-

Please help!

Thanks and Best Regards,

Darius