DATEDIFF in hours and minits In Decimal Format

• skb 44459

  SSCarpal Tunnel

  Points: 4768

  More actions

  October 3, 2013 at 6:09 am

  #299454

  Calculating Difference between ClockIn and ClockOut by Job.

  Table : JobTran

  Job# ClockIn ClockOut

  ---------------------------------------------------------------------

  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

  if the ClockOut is NULL I would use GETDATE() to calculate the difference between ClockIn and ClockOut

  Assuming GETDATE() is 2013-10-03 11:15:34.387

  Job1 - 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

  SSCoach

  Points: 15999

  More actions

  October 3, 2013 at 7:30 am

  #1655555

  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-2 DATETIME = GETDATE();

  /* For your Test */ SELECT @getdate-2 = '2013-10-03 11:15:34.387';

  SELECT

  JobNbr,

  SUM(DATEDIFF(SECOND, ClockIn, ISNULL(ClockOut, @getdate-2)) / 60. / 60.) AS ClockTime

  FROM

  JobTran

  GROUP BY

  JobNbr;

  

  The SQL Guy @ blogspot[/url]

  @SeanPearceSQL

  About Me[/url]