Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Error converting data type varchar to numeric...
Add to briefcase
17 posts, Page 1 of 2
12»»

Error converting data type varchar to numeric Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
DougG
Posted Tuesday, February 05, 2013 2:12 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 06, 2013 12:47 PM
Points: 28, Visits: 99
I get this error when using this function:
CREATE FUNCTION dbo.[PSBPR_FormatData] (@DataValue  DECIMAL(15,4),
				@DataType	  NCHAR(1))
RETURNS VARCHAR(20)
AS
BEGIN

	DECLARE @FormattedData  VARCHAR(20)
		
	SELECT @FormattedData = CASE WHEN @DataType = 'B' --Currency in Billions
				 THEN '$' + SPACE(7 - LEN(@DataValue)) + @DataValue
				 WHEN @DataType = 'C' --Currency
				 THEN '$' + SPACE(10 - LEN(SUBSTRING(CONVERT(VARCHAR(20),@DataValue,1),1, PATINDEX('%.%',CONVERT(VARCHAR(20),@DataValue,1))-1))) 
					  + SUBSTRING(CONVERT(VARCHAR(20),@DataValue,1),1, PATINDEX('%.%',CONVERT(VARCHAR(20),@DataValue,1))-1)
				 --WHEN @DataType = 'N' --Number
				 --THEN SUBSTRING(CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1),1, PATINDEX('%.%',CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1))-1)
				 --WHEN @DataType = 'P' --Percentage
				 --THEN CONVERT(VARCHAR,CAST(100.0 * @DataValue AS DECIMAL(10,1)))  + '%'
				 --WHEN @DataType = 'T' --Currency in Thousands
				 --THEN '$' + SPACE(10 - LEN(SUBSTRING(CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1),1, PATINDEX('%.%',CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1))-1))) 
				--	  + SUBSTRING(CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1),1, PATINDEX('%.%',CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1))-1)
				 ELSE CONVERT(VARCHAR,@DataValue) --'D' = Days, 'H' = 'Hours, 
			 END

	RETURN @FormattedData

END

I know the issue occurs when @DataType = 'C' (probably in other types as well, but if I can get 'C' working...)

I pulled the offending code in the function out and put it in a query:
SELECT '$' + SPACE(10 - LEN(SUBSTRING(CONVERT(VARCHAR,CAST(ROUND([Target],0) AS MONEY),1),1, PATINDEX('%.%',CONVERT(VARCHAR,CAST(ROUND([Target],0) AS MONEY),1))-1))) 
		+ SUBSTRING(CONVERT(VARCHAR,CAST(ROUND([Target],0) AS MONEY),1),1, PATINDEX('%.%',CONVERT(VARCHAR,CAST(ROUND([Target],0) AS MONEY),1))-1) FormattedTarget,
		CONVERT(DECIMAL(15,4),[Target]) ConvertedTarget, [Target] ActualTarget	   
  FROM PSBPR_Tree T
 WHERE CASE WHEN ISNULL(T.Target, 'N/A') IN ('','N/A', 'TBD')
			THEN 'NOT'
			ELSE T.Target
		END != 'NOT'
   AND DataType = 'C'

The code works as desired in the query:
FormattedTarget	ConvertedTarget	ActualTarget
$       500	500.0000	500
$       500	500.0000	500
$     2,000	2000.0000	2000


I have limited what gets passed to the function to the exact same values as the above query:
SELECT CASE WHEN ISNULL(T.Target, 'N/A') IN ('','N/A', 'TBD')
			THEN 'NOT'
			ELSE dbo.PSBPR_FormatData(T.Target, T.DataType)
		END FormattedTarget
  FROM PSBPR_Tree T
 WHERE CASE WHEN ISNULL(T.Target, 'N/A') IN ('','N/A', 'TBD')
			THEN 'NOT'
			ELSE T.Target
		END != 'NOT'
   AND DataType = 'C'

and get the error message: Error converting data type varchar to numeric

I have altered the function to verify the [Target] values pass to the function and implicitly convert to DECIMAL(15,4).

Any help will be much appreciated.
Post #1416096
ScottPletcher
Posted Tuesday, February 05, 2013 4:09 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 3:56 PM
Points: 1,324, Visits: 1,778
Code below should take care of the 'C' type.

The 'B' type is attempting to concatenate chars and numerics, which will always cause a conversion error, so it definitely needs changed too. I don't know what format you wanted for Billions so I didn't change that code.


CREATE FUNCTION dbo.[PSBPR_FormatData] (@DataValue  DECIMAL(15,4),
				@DataType	  NCHAR(1))
RETURNS VARCHAR(20)
AS
BEGIN
RETURN (
    SELECT
	    CASE WHEN @DataType = 'B' --Currency in Billions
		 THEN '$' + SPACE(7 - LEN(@DataValue)) + @DataValue --<<--!! will always cause conversion error!!
		 WHEN @DataType = 'C' --Currency
		 THEN '$' + RIGHT(SPACE(10) + LEFT(CONVERT(varchar(20),CAST(@DataValue AS money),1), LEN(CONVERT(varchar(20),CAST(@DataValue AS money),1)) - 3), 10)
		 ELSE CONVERT(VARCHAR,@DataValue) --'D' = Days, 'H' = 'Hours, 
		 --WHEN @DataType = 'N' --Number
		 --THEN SUBSTRING(CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1),1, PATINDEX('%.%',CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1))-1)
		 --WHEN @DataType = 'P' --Percentage
		 --THEN CONVERT(VARCHAR,CAST(100.0 * @DataValue AS DECIMAL(10,1)))  + '%'
		 --WHEN @DataType = 'T' --Currency in Thousands
		 --THEN '$' + SPACE(10 - LEN(SUBSTRING(CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1),1, PATINDEX('%.%',CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1))-1))) 
		--	  + SUBSTRING(CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1),1, PATINDEX('%.%',CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1))-1)
	     END
)
END

SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson
Post #1416145
DougG
Posted Wednesday, February 06, 2013 5:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 06, 2013 12:47 PM
Points: 28, Visits: 99
Thanks for your response.

I made the change you suggested and still get the conversion error.
Post #1416434
ChrisM@Work
Posted Wednesday, February 06, 2013 5:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:27 AM
Points: 5,618, Visits: 10,990
T.Target isn't one of the numeric datatypes. You're only trapping one string value from being bassed in to the function, are you sure there are not more? Have you tried declaring the @DataValue parameter as VARCHAR and testing/converting within the function?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1416441
DougG
Posted Wednesday, February 06, 2013 5:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 06, 2013 12:47 PM
Points: 28, Visits: 99
ChrisM@Work (2/6/2013)
T.Target isn't one of the numeric datatypes. You're only trapping one string value from being bassed in to the function, are you sure there are not more? Have you tried declaring the @DataValue parameter as VARCHAR and testing/converting within the function?


Thanks for your response.

T.Target gets converted to decimal(15,4) when passed to the function.
I have verified this conversion happens by modifying the function to give back the result without doing any work:
SELECT @FormattedData = @DataValue

Not sure what you mean by only trapping one string value. The values being passed result in an error in the function, but not in the query.

Yes, I have tried declaring @DataValue as VARCHAR.
Post #1416444
ChrisM@Work
Posted Wednesday, February 06, 2013 6:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:27 AM
Points: 5,618, Visits: 10,990
DougG (2/6/2013)
ChrisM@Work (2/6/2013)
T.Target isn't one of the numeric datatypes. You're only trapping one string value from being bassed in to the function, are you sure there are not more? Have you tried declaring the @DataValue parameter as VARCHAR and testing/converting within the function?


Thanks for your response.

T.Target gets converted to decimal(15,4) when passed to the function.
I have verified this conversion happens by modifying the function to give back the result without doing any work:
SELECT @FormattedData = @DataValue

Not sure what you mean by only trapping one string value. The values being passed result in an error in the function, but not in the query.

Yes, I have tried declaring @DataValue as VARCHAR.


CASE WHEN ISNULL(T.Target, 'N/A') IN ('','N/A', 'TBD')

If column T.Target has values of 'TBD', what other character data does it contain which cannot be implicitly converted to decimal(15,4) - and would raise the error message you are seeing?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1416453
DougG
Posted Wednesday, February 06, 2013 6:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 06, 2013 12:47 PM
Points: 28, Visits: 99
ChrisM@Work (2/6/2013)


CASE WHEN ISNULL(T.Target, 'N/A') IN ('','N/A', 'TBD')

If column T.Target has values of 'TBD', what other character data does it contain which cannot be implicitly converted to decimal(15,4) - and would raise the error message you are seeing?


None, but I have also limited what gets passed to the function and query to the three values: 500, 500, 2000
Post #1416457
ChrisM@Work
Posted Wednesday, February 06, 2013 6:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:27 AM
Points: 5,618, Visits: 10,990
Try using APPLY for prototyping this. When you're done, converting to an inline function is little more than copy & paste:

SELECT 
	t.*, 
	x.*,
	y.*, 
	z.*
FROM PSBPR_Tree t
CROSS APPLY (
	SELECT 
		DataValue = CAST(t.[Target] AS DECIMAL(15,4)), 
		DataType = 'C'
) x
CROSS APPLY (
	SELECT vcDataValue = CONVERT(VARCHAR(20),x.DataValue,1)
) y
CROSS APPLY (
	SELECT FormattedData = CASE 
		WHEN x.DataType = 'B' --Currency in Billions
		THEN '$' + SPACE(7 - LEN(x.DataValue)) + x.DataValue
		
		WHEN x.DataType = 'C' --Currency
		THEN '$' + SPACE(10 - LEN(SUBSTRING(vcDataValue,1, PATINDEX('%.%',vcDataValue)-1))) 
					  + SUBSTRING(vcDataValue,1, PATINDEX('%.%',vcDataValue)-1)
					  
				 --WHEN @DataType = 'N' --Number
				 --THEN SUBSTRING(CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1),1, PATINDEX('%.%',CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1))-1)
				 --WHEN @DataType = 'P' --Percentage
				 --THEN CONVERT(VARCHAR,CAST(100.0 * @DataValue AS DECIMAL(10,1)))  + '%'
				 --WHEN @DataType = 'T' --Currency in Thousands
				 --THEN '$' + SPACE(10 - LEN(SUBSTRING(CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1),1, PATINDEX('%.%',CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1))-1))) 
				--	  + SUBSTRING(CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1),1, PATINDEX('%.%',CONVERT(VARCHAR,CAST(ROUND(@DataValue,0) AS MONEY),1))-1)

		ELSE CONVERT(VARCHAR,DataValue) --'D' = Days, 'H' = 'Hours, 
		END
) z

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1416463
DougG
Posted Wednesday, February 06, 2013 6:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 06, 2013 12:47 PM
Points: 28, Visits: 99
No good.
Same error on the final cross apply.

When I comment out the last cross apply, I do get results for t, x, and y.
Post #1416473
ChrisM@Work
Posted Wednesday, February 06, 2013 6:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:27 AM
Points: 5,618, Visits: 10,990
DougG (2/6/2013)
No good.
Same error on the final cross apply.

When I comment out the last cross apply, I do get results for t, x, and y.


Have another read of Scott's post.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1416476
« Prev Topic | Next Topic »

Add to briefcase
17 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.