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