none
Arithmetic overflow error converting expression to data type nvarchar.

    Question

  • Good Day 

    i have been hit by this error this night. 

    I have a UDF defined like this 
     
    CREATE FUNCTION [dbo].[funcFormatPercentages_Extended_numeric]
    (
    
     -- Add the parameters for the function here
    
     @parPercentageToBeFormatted nvarchar(20)
    
    ) 
    RETURNS nvarchar(20)
    
    AS
    
    BEGIN 
    	 RETURN CAST(CAST(convert(float,@parPercentageToBeFormatted)* 100 AS decimal(4, 1)) AS nvarchar(5)) + N'%'  
    END 



    so i will call the UDF with the value like this 

     
    select [dbo].[funcFormatPercentages_Extended_numeric]('-43.4703076923077') 
    
    OR 
     
    select [dbo].[funcFormatPercentages_Extended_numeric]('36.403813624481') 
     
    i get an Error 

    Msg 8115, Level 16, State 2, Line 1
    Arithmetic overflow error converting expression to data type nvarchar.

    I tried to change the datatypes and Precision around , but still the error. 

    Thanks 

     

    Vuyiswa Maseko


    Tuesday, March 12, 2013 8:21 PM

Answers

  • Try this:

    CAST(CAST(convert(float,@parPercentageToBeFormatted)* 100 AS decimal(18, 14)) AS nvarchar(20)) + N'%'  

    Tuesday, March 12, 2013 8:44 PM
    Moderator
  • This statement will guide you to see what the issue is.

    SET NOCOUNT ON;
    USE tempdb;
    GO
    SELECT
    	c1,
    	CAST(c1 AS float) AS c2,
    	CAST(c1 AS float) * 100 AS c3,
    	CAST(CAST(c1 AS float) * 100 AS decimal(5, 1)) AS c4,
    	CAST(CAST(CAST(c1 AS float) * 100 AS decimal(5, 1)) AS nvarchar(7)) AS c5
    FROM
    	(VALUES ('-43.4703076923077')) AS T(c1);
    GO

    Casting the parameter to [float] and multiplying it by 100 yields a number that at least needs (p, s) equal to (5, 1) to convert it to decimal. If you want to convert it to characters then you will need at least 7 characters to represent also the sign and the decimal separator (-4347.0).


    AMB

    Some guidelines for posting questions...

    Tuesday, March 12, 2013 8:52 PM
    Moderator

All replies

  • Try this:

    CAST(CAST(convert(float,@parPercentageToBeFormatted)* 100 AS decimal(18, 14)) AS nvarchar(20)) + N'%'  

    Tuesday, March 12, 2013 8:44 PM
    Moderator
  • This statement will guide you to see what the issue is.

    SET NOCOUNT ON;
    USE tempdb;
    GO
    SELECT
    	c1,
    	CAST(c1 AS float) AS c2,
    	CAST(c1 AS float) * 100 AS c3,
    	CAST(CAST(c1 AS float) * 100 AS decimal(5, 1)) AS c4,
    	CAST(CAST(CAST(c1 AS float) * 100 AS decimal(5, 1)) AS nvarchar(7)) AS c5
    FROM
    	(VALUES ('-43.4703076923077')) AS T(c1);
    GO

    Casting the parameter to [float] and multiplying it by 100 yields a number that at least needs (p, s) equal to (5, 1) to convert it to decimal. If you want to convert it to characters then you will need at least 7 characters to represent also the sign and the decimal separator (-4347.0).


    AMB

    Some guidelines for posting questions...

    Tuesday, March 12, 2013 8:52 PM
    Moderator