Convert a convert

[NewbiDoobie]

I am trying to do an insert, however this is not working the way I expected.

Case when liter is not null then '-' else
convert(varchar(5),(convert(numeric (18,1), Liter) * 1.0567) End as Quarts

I had expected to conver a varchar(4) to conver to a number if it was not null, multiply it out by the conversion , then convert it back to a varchar

It keeps giving me errors

 

[SQLDenis]

Case when liter is null then '-' else
convert(varchar(5),(convert(numeric (18,1), Liter) * 1.0567) End as Quarts

you have to take out the not

http://sqlservercode.blogspot.com/

 

[gmmastros]

It looks like you have a Parenthesis problem. I count 5 open parens and 4 close parens.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[NewbiDoobie]

I changed it, but now I am getting another error:
Server: Msg 8115, Level 16, State 5, Line 1
Arithmetic overflow error converting numeric to data type varchar.

Here is the new staement:\

select
Case when Ltrs is null then '-' else
convert(varchar(4),(convert(numeric (18,1), Ltrs) * 1.0567)) End as Quarts
from tbl1
where ltrs is not null

 

[vongrunt]

Here is example why it won't work:

-- works:
select convert(varchar(4), 2.34)

-- fails:
select convert(varchar(4), 2.341)

So... either make varchar larger, or do some rounding math that ensures no more than 4 characters (dot and - sign included)

And since result is numerical, I don't see the point in conversion to varchar(4) anyway (?)

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLBill]

Your numeric value is 18 characters in length but your varchar is only allowing 4.

-SQLBill

Posting advice: FAQ481-4875