## DATEDIFF in hours and minits In Decimal Format

 Author Message skb 44459 SSC-Enthusiastic Group: General Forum Members Points: 115 Visits: 267 Calculating Difference between ClockIn and ClockOut by Job.Table : JobTranJob# ClockIn ClockOut---------------------------------------------------------------------Job1 2013-10-03 06:17:34.387 NULLJob1 2013-10-03 05:17:34.387 2013-10-03 08:10:34.387Job2 2013-10-03 09:17:34.387 2013-10-03 10:05:34.387Job2 2013-10-03 09:17:34.387 NULLif the ClockOut is NULL I would use GETDATE() to calculate the difference between ClockIn and ClockOutAssuming GETDATE() is 2013-10-03 11:15:34.387Job1 - 7.92 total hours (4.96 from 1st record, 2.96 from second record )Job2 - 2.76 total hours (0.8 from 1st record, 1.96 from second record ) Sean Pearce Ten Centuries Group: General Forum Members Points: 1170 Visits: 3432 `CREATE TABLE JobTran (JobNbr VARCHAR(10), ClockIn DATETIME NOT NULL, ClockOut DATETIME NULL);INSERT INTO JobTran (JobNbr, ClockIn, ClockOut)VALUES ('Job1', '2013-10-03 06:17:34.387', NULL), ('Job1', '2013-10-03 05:17:34.387', '2013-10-03 08:10:34.387'), ('Job2', '2013-10-03 09:17:34.387', '2013-10-03 10:05:34.387'), ('Job2', '2013-10-03 09:17:34.387', NULL);``DECLARE @GetDate DATETIME = GETDATE();/* For your Test */ SELECT @GetDate = '2013-10-03 11:15:34.387';SELECT JobNbr, SUM(DATEDIFF(SECOND, ClockIn, ISNULL(ClockOut, @GetDate)) / 60. / 60.) AS ClockTimeFROM JobTranGROUP BY JobNbr;` The SQL Guy @ blogspot@SeanPearceSQLAbout Me