[SA 9.0.162] Results grid shows too many decimal places

[SA 9.0.162] Results grid shows too many decimal places

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

Re: [SA 9.0.162] Results grid shows too many decimal places

double post

When you mouse over that column's header in the data-grid, what data type is shown in the hint?

Second question, which database drivers do you use in SSMS and in SQL Editor? Are they different?

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.

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).

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))

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".

Casting or conversion is not required in SSMS.

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

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.

	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.