converting from char(8) to smallmoney

[cybercop23]

Hi all.
I have some data that I'm importing from an ODBC. The source data is money represented as 123456 (1234.56). I import it as char(8) but need to display it to the user as 1,234.56.

I tried convert, but it truncates the cents:
CONVERT(smallmoney, Amount/100) 123456 -> 1234.0000
CAST(Amount/100 as smallmoney) 123456 -> 1234.0000

Any suggestions?

 

[gmmastros]

Looks like integer math to me. try....

CONVERT(smallmoney, Amount/100.00) 123456 -> 1234.0000

-George

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

 

[cybercop23]

George,
That what was in my example. When I divide, it loosed the cents. So 123456 should be 1,234.56, however cast and convert will result in 1234.0000. It looses the 56 cents.

 

[gmmastros]

Sorry, I forgot to remove your comments when I copy/pasted. Open a blanks Query Analyzer window and run this...

Select Convert(SmallMoney, 123456/100) [green]--  1234.0000[/green]
Select Convert(SmallMoney, 123456/100[red].0[/red])[green]--  1234.5600[/green]

The .0 causes sql server to do floating point math instead of integer math.

-George

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

 

[gmmastros]

Also forgot to mention how to put the commas in every 3 characters (to the left of the decimal).

Select [blue]Convert(VarChar(8), [/blue]Convert(SmallMoney, 123456/100.0)[blue], 1)[/blue] [green]-- 1,234.56[/green]

-George

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

 

[cybercop23]

Thanks much George. Had to cast the Amount from char(8) to numeric, and it worked.