Converting fields - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Converting fields

Author

Topic

divan
Posting Yak Master

153 Posts

Posted - 2012-09-05 : 08:17:43

I have a temp file that has a amount field that is populated by the following script line

SELECT
RIGHT(REPLICATE(' ',15) + CONVERT(VARCHAR,SUM(D.BILLED_PREMIUM)),15) AS CONVERTED_AMT

Then I am using the temp file to populate another table where the amount field is defined as CONVERTED_AMT CHAR(15) and I use the following case command

CASE WHEN CONVERTED_AMT < 0 THEN RIGHT(REPLICATE(' ',15) + CONVERT(VARCHAR,(CONVERTED_AMT *-1)),15) ELSE RIGHT(REPLICATE(' ',15) + CONVERT(VARCHAR,(CONVERTED_AMT)),15) END AS CONVERTED_AMT ,

When I do that I get the following error message

Conversion failed when converting the varchar value ' 471478.00' to data type int.

Please let me know what I am doing wrong..

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-05 : 08:26:09

This may not necessarily be the problem, but it is recommended that you always specify a length for VARCHAR: http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length.aspx

CASE WHEN CONVERTED_AMT < 0 THEN RIGHT(REPLICATE(' ',15) + 
CONVERT(VARCHAR(32),(CONVERTED_AMT *-1)),15) ELSE RIGHT(REPLICATE(' ',15) + 
CONVERT(VARCHAR(32),(CONVERTED_AMT)),15) END AS CONVERTED_AMT ,

divan
Posting Yak Master

153 Posts

Posted - 2012-09-05 : 08:30:35

Thanks Sunitabeck but that did not resolve the issue.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-05 : 09:31:49

What is the data type of CONVERTED_AMT? Don't know if it got in there when you typed the message, or if the data really has a space character as the first character. If it does have leading or trailing spaces, this may work.

CASE WHEN LTRIM(RTRIM(CONVERTED_AMT)) < 0 THEN RIGHT(REPLICATE(' ',15) + 
CONVERT(VARCHAR(32),(LTRIM(RTRIM(CONVERTED_AMT)) *-1)),15) ELSE RIGHT(REPLICATE(' ',15) + 
CONVERT(VARCHAR(32),(LTRIM(RTRIM(CONVERTED_AMT)))),15) END AS CONVERTED_AMT ,

If you have an opportunity to do so, consider using numeric data types for amounts etc.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-09-05 : 09:37:05

Are you sure the table you're inserting into isn't an integer? If so,' 471478.00' can't be converted to an integer, but FLOOR(' 471478.00') can be.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources