Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
patshaw
Posting Yak Master
177 Posts |
 Posted - 2007-09-14 : 18:56:34
|
| I have a table with a FLOAT column that holds prices. My problem is that when a number such as '1.90' is stored, the zero is lost and the number is stored as '1.9'When I query the data, I want numbers such as this to return as '1.90'. How can I do this? |
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-09-14 : 19:46:36
|
The quick fix is: select cast([column] as varchar(8))+'0' from [table]But I'm sure Peso or someone else has a way of doing it without changing datatype?
Future guru in the making. |
 |
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-09-14 : 19:53:59
|
Actually this is better:SELECT CAST([column] as money) from [table]
Future guru in the making. |
|
|
|
patshaw
Posting Yak Master
177 Posts |
Posted - 2007-09-14 : 20:12:38
|
| Got it. I used:LTRIM(STR([column],7,2))Thanks anyway guys. |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-09-14 : 20:46:26
|
quote:
Originally posted by patshaw
Got it. I used:LTRIM(STR([column],7,2))Thanks anyway guys.
That's good, there are so many ways to do the same thing. That's what I love about SQL
Future guru in the making. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-14 : 22:17:23
|
| You ought to do these presentation type issue in the front end application really.Otherwise you send the application a string object, rather than a numeric object.And SQL Server is pretty inefficient at formatting!Kristen |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-09-17 : 01:45:38
|
quote:
Originally posted by patshaw
I have a table with a FLOAT column that holds prices. My problem is that when a number such as '1.90' is stored, the zero is lost and the number is stored as '1.9'When I query the data, I want numbers such as this to return as '1.90'. How can I do this?
Where do you want to show data?If you use front ends like VB, then you can use format function thereFormat(Rs("col"),"###,###.00")MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Display FLOAT value 1.9 as 1.90