Restrict decimals

I have a SQL Query which is returning result as:
1000.0000
2000.0000
I want to restrict it to two decimal places. Which function to use? I
want to show as 1000.00.Formatting of the binary data that SQL Server returns from a query is done by the client
application. What client app are you using? Sometimes you don't have control over the client app and
need to hint or force formatting so client app does it the way you want. This is generally done by
CAST(colname AS datatype) in the SELECT list. What datatype to cast to you need to experiment to
find out (since different client apps has different default formats). However some MUMERIC(x,2)
seems a reasonable guess.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"RP" <rpk.general@.gmail.com> wrote in message
news:1194500049.949584.282260@.v29g2000prd.googlegroups.com...
>I have a SQL Query which is returning result as:
> 1000.0000
> 2000.0000
> I want to restrict it to two decimal places. Which function to use? I
> want to show as 1000.00.
>|||Hi
You could change the datatype to be decimal (n,2) or use cast to force it.
Alternatively just leave the front end to format the value (assuming that
this is not QA!)
John
"RP" wrote:
> I have a SQL Query which is returning result as:
> 1000.0000
> 2000.0000
> I want to restrict it to two decimal places. Which function to use? I
> want to show as 1000.00.
>|||However, I believe that it would be more appropriate to format this kind of
data in the client app where possible. This would take over this conversion
overhead from the SQL Server. Especially for SQL Server servers which has
already heavy workload on it.
--
Ekrem Önsoy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23IQBy0dIIHA.4688@.TK2MSFTNGP06.phx.gbl...
> Formatting of the binary data that SQL Server returns from a query is done
> by the client application. What client app are you using? Sometimes you
> don't have control over the client app and need to hint or force
> formatting so client app does it the way you want. This is generally done
> by CAST(colname AS datatype) in the SELECT list. What datatype to cast to
> you need to experiment to find out (since different client apps has
> different default formats). However some MUMERIC(x,2) seems a reasonable
> guess.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "RP" <rpk.general@.gmail.com> wrote in message
> news:1194500049.949584.282260@.v29g2000prd.googlegroups.com...
>>I have a SQL Query which is returning result as:
>> 1000.0000
>> 2000.0000
>> I want to restrict it to two decimal places. Which function to use? I
>> want to show as 1000.00.
>|||I am using C# 2005. The column data type is decimal (10,2) but when an
expression is retrieved by multiplying with anything this result is
returned.
How to use cast in SQL itself?|||SELECT
CAST(col1*col2 AS desiredType) AS colname
,col3
,...
FROM ...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"RP" <rpk.general@.gmail.com> wrote in message
news:1194518532.283693.123430@.e9g2000prf.googlegroups.com...
>I am using C# 2005. The column data type is decimal (10,2) but when an
> expression is retrieved by multiplying with anything this result is
> returned.
> How to use cast in SQL itself?
>|||Can you illustrate with an example.
On Nov 8, 4:09 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> SELECT
> CAST(col1*col2 AS desiredType) AS colname
> ,col3|||Hi
You would need to know what your desiredType is, then just use the statement
Tibor gave, for instance if it was decimal(15,2)
SELECT
CAST(col1*col2 AS decimal(15,2)) AS colname
,col3
FROM yrTable
This may be clearer!
USE TEMPDB
GO
CREATE TABLE yrTable ( col1 decimal(10,2),
col2 decimal(10,2),
col3 decimal(10,3),
col4 decimal(10,4))
GO
INSERT INTO yrTable ( col1, col2, col3, col4 )
SELECT 2.05, 2.06, 2.005, 2.0005
GO
SELECT col1* col2 AS res1,
CAST(col1* col2 as decimal(15,2)) AS res2,
col1* col3 AS res3,
CAST(col1* col3 as decimal(15,2)) AS res4,
col1* col4 AS res5,
CAST(col1* col4 as decimal(15,2)) AS res6,
col3* col4 AS res7,
CAST(col3* col4 as decimal(15,2)) AS res8
FROM yrTable
/* returns
res1 res2 res3 res4 res5 res6 res7 res8
-- -- -- -- -- -- -- --
4.2230 4.22 4.11025 4.11 4.101025 4.10 4.0110025 4.01
(1 row(s) affected)
*/
"RP" wrote:
> Can you illustrate with an example.
> On Nov 8, 4:09 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > SELECT
> > CAST(col1*col2 AS desiredType) AS colname
> > ,col3
>
>