SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA 9.0.162] Results grid shows too many decimal places

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[SA 9.0.162] Results grid shows too many decimal places
Author Message
Mindflux



Joined: 25 May 2013
Posts: 807
Country: United States

Post [SA 9.0.162] Results grid shows too many decimal places Reply with quote
I have a query with Round(column,2) which should round to 2 decimal places, however the SQL Assistant results pane shows 4 where SSMS results pane works as expected?

I tinkered with this a bit last night thinking I was doing something wrong and then realized I should see if SSMS returns the same thing....




Last edited by Mindflux on Thu Oct 13, 2016 11:20 am; edited 2 times in total
Thu Oct 13, 2016 10:44 am View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 807
Country: United States

Post Re: [SA 9.0.162] Results grid shows too many decimal places Reply with quote
double post
Thu Oct 13, 2016 10:44 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
When you mouse over that column's header in the data-grid, what data type is shown in the hint?
Thu Oct 13, 2016 1:19 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
Second question, which database drivers do you use in SSMS and in SQL Editor? Are they different?
Thu Oct 13, 2016 1:21 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 807
Country: United States

Post Reply with quote
Question #1: Money(9,4)

For the driver you mean what SQL assistant uses?

According to this SSMS uses ADO and SQL Editor uses ODBC. (Connection type?)

However if I change SQL Editor to use ADO it does not change the rounded column.

FYI: if I use Ctrl-Shift-F9 in SSMS (to give me the SQL Assistant result pane it also shows too many digits.
Thu Oct 13, 2016 2:02 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 807
Country: United States

Post Reply with quote
Now it just shows Money, not null.. something changed because before it showed Money(9,4).. but the amount of decimal places is still broken.

Ahh, well the SQL Native Client 11.0 returns Money(9,4) but Driver={SQL Server} returns Money.

Either way they both show too many decimals with my round function in place?

*edit: well now I'm not sure, it's returning Money(9,4) even when I switched back to the Driver={SQL Driver}..... ah ADO seems to show Money, and ODBC Money(9,4).
Thu Oct 13, 2016 2:30 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
If I read it correctly, the data type is money with 4 decimal digits, so it should show 4 decimal digits, which is correct. To show 2 digits, it needs to be cast to DECIMAL(9,2).

Please try
Code:
SELECT CAST(ROUND(1234.56789, 2) AS MONEY), CAST(ROUND(1234.56789, 2) AS DECIMAL(9,2))

Thu Oct 13, 2016 3:18 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 807
Country: United States

Post Reply with quote
SysOp wrote:
If I read it correctly, the data type is money with 4 decimal digits, so it should show 4 decimal digits, which is correct. To show 2 digits, it needs to be cast to DECIMAL(9,2).

Please try
Code:
SELECT CAST(ROUND(1234.56789, 2) AS MONEY), CAST(ROUND(1234.56789, 2) AS DECIMAL(9,2))


Why does round work in SSMS when returned via F5? Is that some locale setting?



Here's that code written above in both SQL Editor (top) and SSMS (below, native query results window)

The datatype in SSMS is just "money".
Thu Oct 13, 2016 3:26 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 807
Country: United States

Post Reply with quote


Casting or conversion is not required in SSMS.
Thu Oct 13, 2016 3:28 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
The round doesn't change the data type. You still get money(9,4). Even if you round to zero.
My guess for money data type SSMS goes a step further and uses the display format configured in Windows Control Panel in the Region and Language settings
Thu Oct 13, 2016 3:35 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
SQL Assistant display format depends on what the database driver tells it about value precision and scale. That's why you see 4 decimal digits there.
Thu Oct 13, 2016 3:39 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 807
Country: United States

Post Reply with quote
SysOp wrote:
My guess for money data type SSMS goes a step further and uses the display format configured in Windows Control Panel in the Region and Language settings


Maybe SQL Assistant should do that too.
Thu Oct 13, 2016 6:34 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.