title: Return greater then 24 hours in hh:mm:ss format in SQL server 2008
body: <p>i have <code>View</code> in <code>SQL</code> name as <code>vw_Rept_Attend</code> and column names as </p>
  
  <pre><code> UserID (int),WorkHrs (varchar),ExtraHrs varchar(), AtnDate (datetime), EmpName (varchar), 
  EmpType (varchar), UserName (varchar),Role (varchar)
  </code></pre>
  
  <p>actual working on <code>WorkHrs</code> and <code>ExtraHrs</code> </p>
  
  <p>so what i am doing with this is </p>
  
  <p>i stored daily working time of employees in <code>WorkHrs</code> and extra hours in <code>ExtraHrs</code>, then i am showing attendance summary report between two dates provided through filter,</p>
  
  <p>obviously i did maximum of things with your experts suggestions and application is working fine for small data and on client testing i got an issue that i didn't consider at that time</p>
  
  <p><strong>Issue description</strong></p>
  
  <p>every employee has to work 9 hours per day (daily work hours)</p>
  
  <p>working hours (login time)</p>
  
  <p>extra hours (daily work hours - working hours)</p>
  
  <p>if employee works <code>6 hours</code> so his extra hours will be <code>-3 hours</code> negative because of less working time than required time and i got this result by this <code>c#</code> statement </p>
  
  <pre><code>wrktime= workinghrs.Subtract(Convert.ToDateTime(time).TimeOfDay);
  TimeSpan Daily_work = new TimeSpan(9, 0, 0);
   TimeSpan exthrs = Convert.ToDateTime(wrktime).TimeOfDay.Subtract(Daily_work);
  </code></pre>
  
  <p>and this will give me in negative when any one worked less than <code>9 hours</code></p>
  
  <p>so if user do this for <code>300 days</code> and every day <code>-3 hours</code> are added to <code>database</code> result will be <code>-900 hours</code> for <code>300 days</code> </p>
  
  <p>i read all this record using following <code>sqlprocedure</code> to show on crystal report</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[sp_Rpt_Emps_Attnd]
      @strt date,
      @end date
  AS
  
      begin
      with cte
  as
  ( select   UserID,
              WorkHrs,
           case when left(ExtraHrs,1)='-' then -1 else 1 end as multiply,
              right(ExtraHrs,8) as timestring,
              --get hours in seconds:
              DATEPART(HOUR,right(ExtraHrs,8)) * 3600 AS h_in_s,
              --get minutes in seconds:
              DATEPART(MINUTE,right(ExtraHrs,8)) * 60 AS m_in_s,
              --get seconds:
              DATEPART(SECOND,right(ExtraHrs,8)) AS s,AtnDate,EmpName,EmpType,
              UserName, Role,StartDate,EndDate
  
    from      vw_Rept_Attend
  )
  
  select   UserID,dbo.udfTimeSpanFromSeconds(Sum(Left(workhrs,2) * 3600 + substring(Convert(varchar(8),workhrs), 4,2) * 60 + substring(Convert(varchar(8),workhrs), 7,2))) as WorkHrs ,
  case when sum((c.h_in_s + c.m_in_s + c.s) * multiply) &lt; 0
              then '-' + CONVERT(varchar,DATEADD(s,ABS(sum((c.h_in_s + c.m_in_s + c.s) * multiply)),0),114) 
  
              else CONVERT(varchar,DATEADD(s,sum((c.h_in_s + c.m_in_s + c.s) * multiply),0),114)
          end as ExtraHrs ,EmpName,EmpType,UserName, Role,convert(VARCHAR(10),StartDate,105) as StartDate,convert(VARCHAR(10),EndDate,105) as EndDate
  
  from    cte c  where  convert(date,AtnDate) between @strt and @end   
  group by UserID,EmpName,EmpType,UserName, Role,StartDate,EndDate
    Order by UserID
      end
  </code></pre>
  
  <p>and function for calculating sum of all <code>WorkHrs</code> between dates as</p>
  
  <pre><code>    ALTER FUNCTION udfTimeSpanFromSeconds(
          @sec INT
      )
      RETURNS VARCHAR(15)
      AS
      BEGIN
  
  RETURN 
      CONVERT(VARCHAR(10), (@sec / 3600)) + ':' +
      RIGHT('0' + CONVERT(VARCHAR(2), ((@sec % 3600) / 60)), 2) + ':' +
      RIGHT('0' + CONVERT(VARCHAR(2), (@sec % 60)), 2)
  END
  </code></pre>
  
  <p>this i got from my question on <code>stack</code> <a href="https://stackoverflow.com/questions/31429701/how-to-format-time-from-ddhhmmss-to-only-hhmmss-in-sql-server">How to format time from dd:hh:mm:ss to only hh:mm:ss in SQL server?</a></p>
  
  <p>but the portion of calculating sum of <code>ExtHrs</code> which is part of <code>stored procedure</code> which i mention above i-e</p>
  
  <pre><code>case when left(ExtraHrs,1)='-' then -1 else 1 end as multiply,
              right(ExtraHrs,8) as timestring,
              --get hours in seconds:
              DATEPART(HOUR,right(ExtraHrs,8)) * 3600 AS h_in_s,
              --get minutes in seconds:
              DATEPART(MINUTE,right(ExtraHrs,8)) * 60 AS m_in_s,
              --get seconds:
              DATEPART(SECOND,right(ExtraHrs,8)) AS s
  </code></pre>
  
  <p>only support <code>24 hours</code>e-g if result is <code>27 hours</code> then it display <code>3 hours</code> and if it is <code>-56 hours</code> then it display <code>-8 hours</code> </p>
  
  <p>i use <code>SQL Server 2008</code>,</p>
  
  <p>i think enough description to understand but if any of you don't understand then please freely ask me to share code or any logic that i used.</p>
  
  <p>your help will be appreciated,</p>
  
  <p>thanks in advance.</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>this question <a href="https://stackoverflow.com/questions/8503825/what-is-the-correct-sql-type-to-store-a-net-timespan-with-values-240000">What is the correct SQL type to store a .Net Timespan with values &gt; 24:00:00?</a> is totally different because i am not asking about which <code>datatype</code> should i use</p>
  
  <p>here application can get negative time value e-g -3 hours that i explained very clearly and <code>timespan</code> datatype do not accept negative sign.</p>
  
  <p>now i think it should clear</p>
  
  <p><strong>Update</strong>
  in my database it looks like</p>
  
  <p><a href="https://i.stack.imgur.com/LZS3R.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/LZS3R.jpg" alt="enter image description here"></a></p>
  
  <p>so after calculation <code>ExtHrs</code> should be <code>-31:50:46</code> but it display <code>-7:50:46</code> </p>
  
  <p>i think this is because of not supporting greater than <code>24 hours</code></p>