locked
Arithmetic overflow error converting numeric to data type varchar. RRS feed

  • Question

  • I have created the following function 

    USE [MtsGoData]
    GO
    
    /****** Object:  UserDefinedFunction [dbo].[goConvertMinutesToHours]    Script Date: 22/05/2019 07:09:15 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    
    ALTER FUNCTION [dbo].[goConvertMinutesToHours]
    (
        @Minutes int 
    )
    RETURNS decimal(15,2)
    
    AS
    BEGIN
    declare @hours  nvarchar(20)
    
    SET @hours = 
        CASE
    	 WHEN @minutes = 0 then '0.00'
    	  
        ELSE 
            CAST((CONVERT(NUMERIC(18, 2), floor(@Minutes / 60) + ((@Minutes % 60) / 100.0))) AS VARCHAR(2)) 
        END
    
    return cast(@hours as  decimal(10,2));
    END
    
    
    GO
    When I run the following sql given below ,  it is showing the error 'Arithmetic overflow error converting numeric to data type varchar. Please help

    select [dbo].[goConvertMinutesToHours](300);


    polachan


    • Edited by polachan Wednesday, May 22, 2019 6:13 AM enhancement
    Wednesday, May 22, 2019 6:12 AM

All replies

  • Hi polachan,

     

    Please try following script.

     
    alter  FUNCTION [dbo].[goConvertMinutesToHours]
    (
        @Minutes int 
    )
    RETURNS decimal(15,2)
    
    AS
    BEGIN
    declare @hours  nvarchar(20)
    
    SET @hours = 
        CASE
    	 WHEN @minutes = 0 then '0.00'
    	  
        ELSE 
            CAST((CONVERT(NUMERIC(18, 2), floor(@Minutes / 60) + ((@Minutes % 60) / 100.0))) AS VARCHAR(20)) 
        END
    
    return cast(@hours as  decimal(10,2));
    END
    GO
    
    select [dbo].[goConvertMinutesToHours](300);
    /*
    ---------------------------------------
    5.00
    */

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 22, 2019 6:30 AM
  • VARCHAR(2) is too small, use 20 instead.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Wednesday, May 22, 2019 6:31 AM