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 |
|
tfrith
Starting Member
2 Posts |
 Posted - 2002-08-12 : 13:14:19
|
| I've got a column of float values that I want to return rounded to 2 decmial places and only displaying 2 decimal places. For example, 123.4 would be 123.40, 123 would be 123.00 and 123.456 would be 123.46, etc. And I need it as a varchar value.Right now this is done with CONVERT(varchar, CAST(ROUND(floatvalue, 2) AS money)) which has been working fine.BUT, a user now needs to enter this value: 10000000000000000 (yes that is 16 zeros) and this overflows the money datatype.I tried this ugly thing (replace the occurrances of 'plus' with plus signs):LEFT(CONVERT(varchar, ROUND(floatvalue, 2)) plus '00', CHARINDEX('.', CONVERT(varchar, ROUND(floatvalue, 2))) plus 2)This returned '1e' because the large number is returned from a query as 1.0Eplus16.Any thoughts on how I can do this formatting in an update statement?(In the meantime I'm letting the user know that this is probably not a valid value for this application!). |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-08-12 : 13:35:19
|
| Why not convert it to a decimal first....select convert(varchar,convert(decimal(20,2),round(123.4,2)))select convert(varchar,convert(decimal(20,2),round(123,2)))select convert(varchar,convert(decimal(20,2),round(123.456,2)))select convert(varchar,convert(decimal(20,2),round(10000000000000000,2)))Jay White{0} |
 |
|
|
tfrith
Starting Member
2 Posts |
Posted - 2002-08-12 : 14:46:09
|
| That works like a charm.I've never worked with the decimal data type in sql server before so I hadn't considered that option.Thanks. |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-08-12 : 16:11:16
|
quote:
(replace the occurrances of 'plus' with plus signs)
It's only the preview that's broken: + signs post fine. |
|
|
|
|
|
|
|
|
Formatting LARGE float values