title: Arithmetic overflow error converting numeric to data type varchar. while passing large date differance
body: <p>My stored procedure:</p>
  
  <pre><code>ALTER procedure [dbo].[performance]
  (@startdate nvarchar(100), @enddate nvarchar(100)
  as begin
      declare @date1 nvarchar(100) = convert(varchar, @startdate+' 00:00:00.000', 120)
  
      declare @date2 nvarchar(100) = convert(varchar, @enddate+' 23:59:59.000', 120)
  
      set NOCOUNT on;
      select l.LocName,v.Vtype, SUM(DATEDIFF(MI,t.Paydate,t.DelDate)) as TotalDiff,
      [dbo].[testfunction](
  CONVERT(decimal(10,1), 
  AVG( CONVERT(NUMERIC(18,2), DATEDIFF(SS,t.Paydate,t.DelDate) ) )))  as Average
      from Transaction_tbl t
      left join VType_tbl v
          on t.vtid=v.vtid
      left join Location_tbl l
          on t.Locid=l.Locid
      where t.Locid in
          (select t1.Locid from Transaction_tbl t1)
      and dtime between '' + @date1 +'' and ''+ @date2 +''
      and Status =5
      group by v.Vtype,l.LocName,l.Locid order by l.Locid
  end
  </code></pre>
  
  <p>My function:</p>
  
  <pre><code>ALTER FUNCTION [dbo].[testfunction] (@dec NUMERIC(18, 2)) RETURNS Varchar(50) 
  AS
  BEGIN  DECLARE @hour integer,  @Mns integer,   
  @second decimal(18,3)DECLARE @Average  Varchar(50)  
  select @hour=CONVERT(int,@dec/60/60) 
  SELECT @Mns = convert(int, (@dec / 60) - (@hour * 60 ));
  select @second=@dec % 60; 
  
  SELECT @Average = convert(varchar(9), convert(int, @hour)) + ':' +  right('00' + convert(varchar(8), convert(decimal(18,2), @Mns)), 2) + ':' +
  right('00' + CONVERT(decimal(10,0), convert(varchar(10), @second)), 6)
  RETURN @Average end
  </code></pre>
  
  <p>if i pass start date:2013-06-01 and end date:2013-08-01 then getting proper out put if i pass start date:2010-06-01 and end date:2013-08-01 (bigger date difference) then getting error: what is wrong with my function</p>