title: Query Datediff to output HH:MM in SQL Server
body: <p>I'm working with SQL Server 2012, and I have two columns <code>start</code> and <code>end</code> with <code>varchar(5)</code> values in <code>HH:MM</code> format.</p>
  
  <p>The data looks like this</p>
  
  <pre><code>ID    Start       End
  ------------------------
  1     00:00       06:00
  2     06:00       16:00
  3     16:00       18:00
  4     18:00       24:00
  </code></pre>
  
  <p>My query is like this:</p>
  
  <pre><code>SELECT
      a.start,
      a.[end],
      RIGHT('0' + CONVERT(VARCHAR(3), DATEDIFF(MINUTE, a.Start, a.[end]) / 60), 2) + ':' +
      RIGHT('0' + CONVERT(VARCHAR(2), DATEDIFF(MINUTE, a.Start, a.[end]) % 60), 2) AS TotalHours 
  FROM
      TransactionActivity a
  </code></pre>
  
  <p>I exec the query with where clause based on ID number, it gives me the correct result, until in ID 4: i got error like this</p>
  
  <blockquote>
    <p>Conversion failed when converting date and/or time from character string.</p>
  </blockquote>
  
  <p>I think it because the End time value is 24:00, how can I make it to get the time difference?</p>