Reply to topic

Representation decimals Query Analyzer
Vincent Visch


Joined: 20 Jun 2007
Posts: 7
Reply with quote
I have this value in a database with '6,0547', but the Query Analyzer shows this as '6,05'. Is there a way to show the value exactly as it's stored in the database?
View user's profileSend private message
Logan Mueller
Support

Joined: 16 Nov 2003
Posts: 565
Reply with quote
Is this value coming from a MONEY datatype in MS SQL server? It sounds like the localization in Windows regional settings are formatting it this way.

This makes some sense, although an option could be added to display MONEY values as DECIMAL. How does that sound? For the time being, you could change your regional settings to show 4 digits after the decimal. That being a temporary solution, of course, this would change the way money values are displayed in most applications.

_________________
Support is provided as a convenience to our users. We make no guarantees in the timeliness and accuracy in our responses. We reserve the right to refuse service to anyone.
View user's profileSend private messageVisit poster's website
Vincent Visch


Joined: 20 Jun 2007
Posts: 7
Reply with quote
It is a money representation, but as far as I know it's not a MS SQL Server.

The change in the regional settings worked. Why is the Query Analyzer depending on those settings instead of just showing what value is in the DB?
View user's profileSend private message
Logan Mueller
Support

Joined: 16 Nov 2003
Posts: 565
Reply with quote
Hi Vincent,

Unfortunately the database does not pre-format the values. It submits them to the client in binary form-- unless instructed otherwise in the query. This is why the Query Analyzer has to make the string conversion with the regional settings on the client computer.

If you need the value to be formatted on the server, you can always use a T-SQL CONVERT() in the query.

The same issue arises if you select a binary BLOB from the database. Most Query Analyzers will not show you the binary content. They convert it to a hexadecimal string, even though the database is not storing the value as hex.

Another solution to the problem would be to add an option for Query Analyzer to treat the MONEY values as DECIMAL. Showing all the decimal places. The only dilemma with this is that not all countries format their MONEY values the same way they do DECIMAL, and so it can't be the default behavior.

_________________
Support is provided as a convenience to our users. We make no guarantees in the timeliness and accuracy in our responses. We reserve the right to refuse service to anyone.
View user's profileSend private messageVisit poster's website
Logan Mueller
Support

Joined: 16 Nov 2003
Posts: 565
Reply with quote
A new option has been added to v2.5.1 called 'Localize MONEY values as DECIMAL'. Enabling this option will show all numbers after the decimal, regardless of the regional settings, but the only drawback is that MONEY values will not get formatted as currency in this case.

_________________
Support is provided as a convenience to our users. We make no guarantees in the timeliness and accuracy in our responses. We reserve the right to refuse service to anyone.
View user's profileSend private messageVisit poster's website
Representation decimals Query Analyzer
All times are GMT - 8 Hours  
Page 1 of 1  

  
  

 Reply to topic