|
Home > Archive > MS SQL Server > July 2005 > How to set the decimal places
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
How to set the decimal places
|
|
|
| I have a calculation in a stored procedure that returns a percentage like (32
* 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @total as float.
I need to set or convert the result to have 2 decimal places like 1.86,
rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
variations (examples from the internet), nothing is working for me. Is there
a way to achieve this in SQL? I would really appreciate the help.
| |
| Dejan Sarka 2005-07-15, 3:23 am |
|
"HLong" <HLong@discussions.microsoft.com> wrote in message
news:C7A3278F-FB47-451C-855E- 8B0978DC5930@microso
ft.com...
>I have a calculation in a stored procedure that returns a percentage like
>(32
> * 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @total as
> float.
> I need to set or convert the result to have 2 decimal places like 1.86,
> rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
> variations (examples from the internet), nothing is working for me. Is
> there
> a way to achieve this in SQL? I would really appreciate the help.
Is this what ou want - SELECT CAST(32.0 * 100/1722 AS decimal(5,2)) ?
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www. SolidQualityLearning
.com
| |
|
| Thanks Dejan. I tried that same function yesterday so many times but I
always got
1.00, which was not good. However, now I tried 32*100.00 and it worked
fine. I don't know why it worked. May be because the 100.00 is taken as a
decimal type, instead of 32*100 where both are int. Could you explain this a
bit more?
"Dejan Sarka" wrote:
>
> "HLong" <HLong@discussions.microsoft.com> wrote in message
> news:C7A3278F-FB47-451C-855E- 8B0978DC5930@microso
ft.com...
>
> Is this what ou want - SELECT CAST(32.0 * 100/1722 AS decimal(5,2)) ?
>
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www. SolidQualityLearning
.com
>
>
>
>
| |
| Dejan Sarka 2005-07-15, 11:23 am |
| > Thanks Dejan. I tried that same function yesterday so many times but I
> always got
> 1.00, which was not good. However, now I tried 32*100.00 and it worked
> fine. I don't know why it worked. May be because the 100.00 is taken as
> a
> decimal type, instead of 32*100 where both are int. Could you explain
> this a
> bit more?
In T-SQL we do not denote data types for literal values like, for example,
in C#. So SQL Server uses it's own logic, and takes 32 and 100 as integers.
First operand data type is then used for result as well. You can also check
topics on data types precendence in Books OnLine.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www. SolidQualityLearning
.com
|
|
|
|
|