Arithmetic overflow error converting expression to data type nvarchar.
-
Good Day i have been hit by this error this night. I have a UDF defined like this
CREATE FUNCTION [dbo].[funcFormatPercentages_Extended]
(-- 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'%'
ENDso 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, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa[at]dotnetfunda.com http://www.Dotnetfunda.com
-
Good Day i have been hit by this error this night. I have a UDF defined like this
CREATE FUNCTION [dbo].[funcFormatPercentages_Extended]
(-- 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'%'
ENDso 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, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa[at]dotnetfunda.com http://www.Dotnetfunda.com
A guess only, try removing the N'%' and see if it makes a difference. % is an operator and the interpretor may be making a mistake on the operation.
Never underestimate the power of human stupidity RAH
-
Good Day i have been hit by this error this night. I have a UDF defined like this
CREATE FUNCTION [dbo].[funcFormatPercentages_Extended]
(-- 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'%'
ENDso 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, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa[at]dotnetfunda.com http://www.Dotnetfunda.com
decimal(4, 1)means a number with three digits before the decimal point and one digit after: http://msdn.microsoft.com/en-gb/library/ms187746.aspx[^] Based on your provided inputs:36.403 * 100 = 3640.3Therefore, your result requires four digits before the decimal point, so the precision should be 5:RETURN CAST(CAST(CONVERT(float, @parPercentageToBeFormatted) * 100 AS decimal(5, 1)) AS nvarchar(5)) + N'%'
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
