Syntax error converting varchar 1.8 to a column of datatype int.

  • Thread starter Mitchell_Collen via AccessMonster.com
  • Start date
M

Mitchell_Collen via AccessMonster.com

Hi. Will you please help me resolve this conversion error?

Syntax error converting varchar 1.8 to a column of datatype int.

I have looked at my data and 1.8 is the first value in the column named:
ResultValueCreatinine. Then I used this function: CONVERT(int,
ResultValueCreatinine)

Will you tell me what could be wrong? Below is my code.


ALTER PROCEDURE Pharmacy.StoredProcedure1
AS SELECT Visit#, MRN, EventTime, ResultNumber, ResultNameCreatinine,
ResultValueCreatinine, Abnormal AS CreatinineAbnormal, ResultNameWeight,
ResultValueWeight, Abonormal AS WeightAbnormal, Sex,
PatientName, Age, AgeType, Height, Race, Status,
'IBW' = CASE WHEN Sex = 'F' THEN (((Height / 2.54) - 60)
* 2.3) + 45.5 WHEN Sex = 'M' THEN (((Height / 2.54) - 60) * 2.3) + 50 END,
'Clearance' = CASE WHEN Sex = 'F' THEN (((140 - [age])
* (((Height / 2.54) - 60) * 2.3) + 45.5) / (CONVERT(int,
[ResultValueCreatinine]) * 72))
* 0.85 WHEN Sex = 'M' THEN (((140 - [age]) * (((Height
/ 2.54) - 60) * 2.3) + 50) / (CONVERT(int, [ResultValueCreatinine]) * 72)) *
0.85 END
FROM Pharmacy.CCreatinineWithWeight
 
S

Sylvain Lafontaine

Convert it first to a real or a float before truncating or rounding to an
int:

declare @s varchar (20)
set @s = '1.8'
select @s, convert (int, convert (float, @s))

You could also extract the integer part of the string before converting it
to an integer.

Also, as you are making floating point calculus with this value
(ResultValueCreatinine), I don't see why you want to convert it to an
integer instead of a real, a floating point or a decimal number.
 
M

Mitchell_Collen via AccessMonster.com

I am sorry. What do you mean by floating calculus? The reason that I was
converting to int is because that is what it said in the error. The values
are all basically like 1.2, 2.3, 1.6 and so on. What do you suggest is best
for these number? I know now, you don't suggest int.
Thanks, Misty
 
S

Sylvain Lafontaine

Numbers like 1.2 and 2.3 are not integers but floating point numbers or
number with a decimal value. Try replacing int with float or real:

.... (CONVERT(float, [ResultValueCreatinine]) ...

instead of:

.... (CONVERT(int, [ResultValueCreatinine]) ...
 
A

aaron.kempf

why would you use float ever anywhere?

I don't get it

Numbers like 1.2 and 2.3 are not integers but floating point numbers or
number with a decimal value.  Try replacing int with float or real:

... (CONVERT(float, [ResultValueCreatinine]) ...

instead of:

... (CONVERT(int, [ResultValueCreatinine]) ...

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)



I am sorry. What do you mean by floating calculus? The reason that I was
converting to int is because that is what it said in the error. The values
are all basically like 1.2, 2.3, 1.6 and so on. What do you suggest is
best
for these number? I know now, you don't suggest int.
Thanks, Misty

- Show quoted text -
 
R

Robert Morley

They let you back on, did they Aaron? Either that or you found a way around
the block MS had on you. Ah well, it was peaceful here for a while!

To anyone who hasn't "met" Aaron before, he's a well-known troll who hasn't
posted in this group for nearly a year now. See his Google Groups profile
for examples of his posts:
http://groups.google.com/groups/pro...AClWkNjdi7eVtyYt8oOaw949h3i3SmjGmAJbX05nZ-8fQ

While admittedly, he has posted useful information on occasion, his
more-common, decidedly-visceral posts prompted MS to block him from the MS
servers, so his posts would only show up for those getting echoes of these
newsgroups from non-MS sources. It seems that that block has expired/been
removed, or Aaron has discovered some other means of posting.



Rob

why would you use float ever anywhere?

I don't get it

Numbers like 1.2 and 2.3 are not integers but floating point numbers or
number with a decimal value. Try replacing int with float or real:

... (CONVERT(float, [ResultValueCreatinine]) ...

instead of:

... (CONVERT(int, [ResultValueCreatinine]) ...

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)



I am sorry. What do you mean by floating calculus? The reason that I was
converting to int is because that is what it said in the error. The values
are all basically like 1.2, 2.3, 1.6 and so on. What do you suggest is
best
for these number? I know now, you don't suggest int.
Thanks, Misty
- Show quoted text -
 
A

aaron.kempf

Rob;

Supposedly; the story is that Microsoft has blocked some of my posts
in the past.

But more importantly; I was working in the Marketing Department at
Microsoft; so I decided to tone it down a little bit.

Thanks

-Aaron

They let you back on, did they Aaron?  Either that or you found a way around
the block MS had on you.  Ah well, it was peaceful here for a while!

To anyone who hasn't "met" Aaron before, he's a well-known troll who hasn't
posted in this group for nearly a year now.  See his Google Groups profile
for examples of his posts:http://groups.google.com/groups/profile?show=more&enc_user=5ySa2xUAAA...

While admittedly, he has posted useful information on occasion, his
more-common, decidedly-visceral posts prompted MS to block him from the MS
servers, so his posts would only show up for those getting echoes of these
newsgroups from non-MS sources.  It seems that that block has expired/been
removed, or Aaron has discovered some other means of posting.

Rob



why would you use float ever anywhere?
I don't get it
Numbers like 1.2 and 2.3 are not integers but floating point numbers or
number with a decimal value.  Try replacing int with float or real:
... (CONVERT(float, [ResultValueCreatinine]) ...
instead of:
... (CONVERT(int, [ResultValueCreatinine]) ...
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
I am sorry. What do you mean by floating calculus? The reason that I was
converting to int is because that is what it said in the error. The values
are all basically like 1.2, 2.3, 1.6 and so on. What do you suggest is
best
for these number? I know now, you don't suggest int.
Thanks, Misty

- Show quoted text -
 
Top