Arithmetic overflow error converting expression to data type nvarchar.

Posted by Vuyiswamb under Sql Server on 3/12/2013 | Points: 10 | Views : 5668 | Status : [Member] [MVP] [Administrator] | Replies : 2
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

Thank you for posting at Dotnetfunda
[Administrator]



Responses

Posted by: Jayakumars on: 3/12/2013 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi
Bro vuys

try this code increase your Decimal Place (6,2) here 2 means after point 6 means remaining values so

Alter 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(6, 2)) AS nvarchar(20)) + N'%'
END

output
===========
-4347.03%

Mark as Answer if its helpful to you

Vuyiswamb, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sqldev on: 3/15/2013 [Member] Starter | Points: 25

Up
0
Down
Hi,

Decimal(4,1) can hold only 4 characters (inlcuding 1 digit after the decimal point and '-' sign ).

So, we can have only 3 digits in the output since 1 character is already used by - (minus) sign. Which mean you can have only 2 digits before decimal point.

But when you mutliply '-43.4703076923077' with 100 = -4347.0
Decimal (4,1) cannot hold this value . So, if you want this output use
DECIMAL(6,1)


One more correction is needed here. In this code you are using nvarchar(5). Instead of that use
NVARCAHR(7)




Please find the below code :


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(6, 1)) AS nvarchar(7)) + N'%'
END




Hope this info is helpful for you

Best Regards,
SQL Blogger
http://learnsqltips.blogspot.com/
http://www.youtube.com/user/TheSSScreations


Vuyiswamb, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response