title: Calculate Sum of Datetime in HH:MM:SS in sql
body: <p>I want to calculate Sum in HH:MM:SS </p>
  
  <p>First i am calculating datetime Difference in HH:MM:SS (works well)</p>
  
  <pre><code>Select  * 
  Into    #_TicketType 
  From    (    
          Select  *,
                  convert(varchar(5), DateDiff(s, [Start Date] , [End Date] )/3600)
                + ':'
                + convert(varchar(5),DateDiff(s, [Start Date] , [End Date])%3600/60)
                + ':'
                + convert(varchar(5),(DateDiff(s,[Start Date] , [End Date])%60)) as [Ticket Type Time]
              ,
                  convert(varchar(5),DateDiff(s, [Ticket Open Time]  , [Ticket Closed Time] )/3600)
                + ':'
                + convert(varchar(5),DateDiff(s,[Ticket Open Time]  , [Ticket Closed Time])%3600/60)
                + ':'
                + convert(varchar(5),(DateDiff(s,[Ticket Open Time]  , [Ticket Closed Time])%60))[Total Call Time]
          FROM CTE
          )_A
  </code></pre>
  
  <p>Columns <code>[Start Date] , [End Date],[Ticket Open Time]  , [Ticket Closed Time]</code> of type <code>DateTime</code></p>
  
  <p>Further i want to calculate sum of it in HH:MM:ss</p>
  
  <p>My Query :-</p>
  
  <pre><code>Select Distinct ATM,
      CAST (
            (SUM (datepart(hh, convert (varchar, [Ticket Type Time], 108)))
          + (sum (datepart(mi, convert (varchar, [Ticket Type Time], 108)))/60) ) 
      AS VARCHAR(2))
      + ':'
      + CAST (
            sum( datepart(mi, convert (varchar, [Ticket Type Time], 108))) 
          - 60 
          * (sum(datepart(mi, convert (varchar, [Ticket Type Time], 108)))/60)
      as VARCHAR(2))
      + ':' 
      + CAST (
            sum( datepart(ss, convert (varchar, [Ticket Type Time], 108))) 
          - 60 
          * (sum( datepart(SS, convert (varchar, [Ticket Type Time], 108)))/60)
      as VARCHAR(2))
  From #_TicketType Group By ATM
  </code></pre>
  
  <p>It Gives me error </p>
  
  <pre><code>**Msg 241, Level 16, State 1, Line 67
  Conversion failed when converting date and/or time from character string.**
  </code></pre>
  
  <p><strong>Datebase SQL SERVER 2008</strong></p>