problem with CAST and CONVERT in SQL Server2000 converting decimal places from 4 to 2

All of my currency columns are only storing 2 decimal places when I insert into the database but when I pull out the data with a SELECT statement, I always get 4 decimal places instead of the 2 that were inserted.

For example:

Database Price SELECT statement Price

100.56 100.5600

I have tried to use the CAST and/or CONVERT commands but I cannot get the output to come out as 100.56. Has anyone had a similar problem?

Thanks

Look at the STR() function in Books Online.

STR(FloatExpression,LengthOverall,PlacesToRightOfDecimal)

|||Usually this type of formatting is done on the client side. You can use any of the string.format functions.|||You application is having a precision problem, it happens in SQL Server, there are two solutions a third party driver that on the TDS(Tabular Data Stream) level correct it or do it the cheap and free way use Decimal or Numeric instead of money and your problems will go away. Numeric is bigger than money but you will not get that problem. Hope this helps.|||I have tried to use the decimal datatype convert but it is rounding up or down and I am trying to show an amount, i.e. price. I just need something that will allow me to show the value as it exists in the database. When I view the data in the table, I only see the two decimal places.|||

Try the format in these links. Hope this helps

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconformattingnumericdataforspecificculture.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstringsoutputexample.asp