How to get total hours in stead of Date time view

• peter2501

  SSChasing Mays

  Points: 601

  More actions

  September 30, 2014 at 3:20 am

  #290444

  Hi,

  I have a close date and an open date (both also with time) so a datetime variable.

  I want to calculate the subtraction from this to see the total time. But I want to see the time in only the total hours:minutes:seconds.

  I have the following query

  Select naam as name, Maandnummer as Month, Org as Organisation, totaal as total, opendate, closedate as solved, closedate-opendate as totaltime from #tickettotal

  And the result is

  name Month Organisation total opendate solved totaltime

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

  Broxxxxxx 9 xxxxx 15236 2014-09-23 08:43:30.647 2014-09-25 07:14:06.437 1900-01-02 22:30:35.790

  Broxxxxxx 9 xxxxxxxx 15154 2014-09-16 11:24:32.823 2014-09-19 10:12:28.367 1900-01-03 22:47:55.543

  Broxxxxxx 9 xxxxxxx 14689 2014-08-19 13:48:29.560 2014-09-18 13:09:47.717 1900-01-30 23:21:18.157

  How do I get the BOLD datetime in only the total hours.

  So f.e. 1900-01-02 22:30:35 should be 46:30:35 (46 hours, 30 minuts, 35 sec)

  If I do datediff etc I always get stuck with the extra day, because it will see the 02 as 2 days, but that is caused by the datetime variable

  thanks in advance

  Peter

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  September 30, 2014 at 4:07 am

  #1749082

  WITH RawData AS (Select naam as name, Maandnummer as Month, Org as Organisation, totaal as total, opendate, closedate as solved, datediff(ss, opendate, closedate) as Seconds from #tickettotal)

  SELECT name, Month, Organisation, total, opendate, solved, CAST((Seconds/3600) AS varchar(4)) + ' hours, ' + CAST((Seconds%3600)/60 AS Varchar(2)) + ' minutes, ' + CAST((Seconds%60) AS VARCHAR(2)) + ' seconds' as TotalDuration

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• peter2501

  SSChasing Mays

  Points: 601

  More actions

  September 30, 2014 at 4:43 am

  #1749087

  Thanks,

  I needed to make some small changes because it was only part of a complete script, but I got it working.

  And also understand the query itself.

  Peter