[firebird-support] DATEDIFF(HOUR...) returns 1 for a 45 minutes interval

[venussof...@gmail.com [firebird-support]]

Hi all

I would have attached a screen shot of the sample data but I guess that is not 
possible.  What I am trying to achieve right now is get the time difference in 
hh:mm format to the user
 

 tStartTime and tEndTime field values

 26.08.2014, 08:38:00.000    26.08.2014, 09:45:00.000
26.08.2014, 10:00:00.000    26.08.2014, 10:45:00.000
26.08.2014, 11:00:00.000    26.08.2014, 12:00:00.000
26.08.2014, 11:30:00.000    26.08.2014, 12:15:00.000
26.08.2014, 00:15:00.000    26.08.2014, 13:30:00.000


 and the following is the query
 SELECT sProdLineWorkProcess.*, (DATEDIFF(HOUR, 
sProdLineWorkProcess.tStartTime, sProdLineWorkProcess.tEndTime)) AS nHoursWorked
        , (DATEDIFF(MINUTE, sProdLineWorkProcess.tStartTime, 
sProdLineWorkProcess.tEndTime) - (DATEDIFF(HOUR, 
sProdLineWorkProcess.tStartTime, sProdLineWorkProcess.tEndTime) * 60)) AS 
nHoursWorked
        , (DATEDIFF(MINUTE, sProdLineWorkProcess.tStartTime, 
sProdLineWorkProcess.tEndTime)) AS nTotalMinutesWorked
    FROM sProdLineWorkProcess
    JOIN tProdLineWork
        ON tProdLineWork.iID = sProdLineWorkProcess.iPID
    WHERE CAST(tProdLineWork.tDt AS DATE) = '2014-08-26'
    --GROUP BY sProdLineWorkProcess.iEmployeeID
    

 The calculated field values are as follows:
 1    7    67
0    45    45
1    0    60
1    -15    45
13    15    795

 

 Please see the 4th row, the diff between 11:30 and 12:15 is 1 Hour and -15 
mins whereas difference between 10:00 and 10:45 is 0 hour and 45 mins.
 

 Please advise on what I need to do correctly to get 0 hour and 45 mins for 
both 2nd and 4th row.
 

 Thanks and regards
 Bhavbhuti