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 |
|
SonicClang
Starting Member
8 Posts |
 Posted - 2009-06-16 : 17:30:47
|
| I've been searching high and low all over the internet for a solution to this. I'm really hoping someone here can help. What I need to do is, in a query/view, force a field to display n numbers past the decimal point. To a lot of the world, 0.0 is the same as 0. But in my world there's a big difference. For instance, if an operator is taking a reading off a scale while weighing a part, and the scale goes to 0.5kg resolution, he has to write 1.0kg, not 1kg. I'm recording data to a database and displaying it on the operator screen at a machine. It's working great except SQL doesn't show ".0". So their numbers look like, 0, 1.5, 2, 2.5... when what they should see is 0.0, 1.5, 2.0, 2.5... etc.I've tried every function I can think of to achieve this. I know just how to do it in Access, I'd use the format function, but SQL doesn't appear to have this. I've tried cast, convert, and any others out there. Does SQL Server 2005 have a format function?The numbers are mainly stored as float. I can round them no problem, and I have no problems with accuracy (a complaint I see some people making about using float). |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-06-16 : 17:49:13
|
| Use the CAST functionCAST(fieldname AS DECIMAL(10,2))"2" represents number of decimal places you want to show.."10" is just an example..u can choose it based on ur data size. |
 |
|
|
SonicClang
Starting Member
8 Posts |
Posted - 2009-06-18 : 15:10:43
|
| If that's what you say should work, and I've seen that in other places (like I said I tried a bunch of ways), then there must be something wrong with my data. That does a good job of rounding the data, but 0 still shows up as "0", not "0.0". I want it to force it to always show one place past the decimal point, even if it's just zeros after it. Can I start with float and do what you're saying? |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-06-18 : 15:52:41
|
quote:
Originally posted by SonicClang
I'm recording data to a database and displaying it on the operator screen at a machine. It's working great except SQL doesn't show ".0". So their numbers look like, 0, 1.5, 2, 2.5... when what they should see is 0.0, 1.5, 2.0, 2.5... etc.
It seems to work just fine for me. Sounds like you are having a display issue. Can you format the value in your presentation layer that displays it to the screen?DECLARE @Foo DECIMAL(18,2)SET @Foo = 0SELECT @Foo-- Result---------------------------------------0.00 |
|
|
|
SonicClang
Starting Member
8 Posts |
Posted - 2009-06-18 : 17:10:29
|
| I'm using the Excel Chart ActiveX plug-in from the Microsoft Web Components to display the information on the operator screen. I just give the chart a data source and it displays whatever the SQL server sends. I'm using a view for this actually. It works out really well because any changes required to the formatting of how the data is displayed can be handled on the SQL server and the next time the operator screen refreshes, it's fixed. I either need to figure out how to make the view display .0, or tell the operators not to worry about it. When the operator shuts down their run, the control program is calling an Access database file that opens, prints a report to a printer, and closes itself (all without the operator even seeing it open), and the Access report is formatted correctly.I think I'm going to give this a try on another SQL server (we have a couple here). |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-06-18 : 17:39:43
|
| Ahh, yes. That would be an Excel issue. I'm not familar with that particular control. But, if you can set it to Text it should preserve the formatting. But, I think by default Excel will format numbers automatically changing 0.00 to 0. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-06-19 : 04:52:37
|
| Absolutely this is an excel issue. Setting to text before inporting the data will fix.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
SonicClang
Starting Member
8 Posts |
Posted - 2009-06-22 : 17:31:24
|
| If I use the CAST function in query analyzer it actually works. But inside a view it doesn't. I hadn't tested in the query analyzer until right now. Why would it work in one place and not the other? |
|
|
|
|
|
|
|
|
SQL query: Force n places past decimal point