Convert() problem

Question

Convert() problem

J M-314995

SSCrazy

Points: 2975
More actions
December 22, 2009 at 11:13 pm

#214928

I'm getting an error on a subquery...
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '0.00 ' to data type int.
Here is the code for the subquery…
,TOT_CHGS =
(
SELECTSUM(CONVERT(INT, ARCHRG.CHRG_BAL))
FROMARCHRG99 ARCHRG (NOLOCK)
WHEREMRPA.PTID = ARCHRG.PTID
AND CONVERT(INT, ARCHRG.CHRG_BAL) > '0'
)
I’ve saw the extra spaces after the zero so I tried the lines…
SUM(CONVERT(INT, RTRIM(ARCHRG.CHRG_BAL)))
…
CONVERT(INT, RTRIM(ARCHRG.CHRG_BAL))
All that got me was this error…
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '0.00' to data type int.
When I change the data type in my convert function to DECIMAL or MONEY I don’t get the error but rather I get NULL results. I tried my script out on one account and there are no null values in the CHRG_BAL column for that one account yet my subquery produces a null value when it should be giving me the number $326.50 on this one specific account.
While searching for possible solutions, I’ve already come across 3 entries that say something to the effect…
“This is precisely the problem you'll get into when the designer of the database picks the wrong data type for the columns. The correct way to handle this is to fix the data, and possibly the data type.”
Since I don’t have the luxury of changing data types, I need to find another way.
TIA,
John

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Nabha SSCrazy Eights Points: 8534 More actions · Answer 1

Not sure what your problem is, but just to help you to help us to help you,(:-)) post the data like this and show us whats your problem

CREATE TABLE #ACCOUNT(num varchar(10))

INSERT INTO #ACCOUNT VALUES ('10.0 ')

INSERT INTO #ACCOUNT VALUES ('10.1 ')

INSERT INTO #ACCOUNT VALUES ('10.2 ')

INSERT INTO #ACCOUNT VALUES (NULL)

Select SUM(convert(MONEY, RTRIM(num))) FROM #ACCOUNT

---------------------------------------------------------------------------------

Mike01 SSChampion Points: 11381 More actions · Answer 2

It's got to do with trying to convert decimal values to integers. Using Nabha's example, try running the following. The Round is performing an implicit conversion for you.

CREATE TABLE #ACCOUNT(num varchar(10))

INSERT INTO #ACCOUNT VALUES ('10.0 ')

INSERT INTO #ACCOUNT VALUES ('10.1 ')

INSERT INTO #ACCOUNT VALUES ('10.2 ')

INSERT INTO #ACCOUNT VALUES (NULL)

select convert(int, Round(num,0)) from #ACCOUNT

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

J M-314995 SSCrazy Points: 2975 More actions · Answer 3

Mike01 (12/23/2009)
It's got to do with trying to convert decimal values to integers. Using Nabha's example, try running the following. The Round is performing an implicit conversion for you.
CREATE TABLE #ACCOUNT(num varchar(10))
INSERT INTO #ACCOUNT VALUES ('10.0 ')
INSERT INTO #ACCOUNT VALUES ('10.1 ')
INSERT INTO #ACCOUNT VALUES ('10.2 ')
INSERT INTO #ACCOUNT VALUES (NULL)
select convert(int, Round(num,0)) from #ACCOUNT

Thanks for the replies. After thinking about it, I should be using MONEY instead of INT. When using MONEY, I'm getting null values when there should be actual amounts. I'll start a new thread about that problem.