Prev: Team Foundation Server and SQL Server
Next: INSERTED table no holding exact float value
From: Sam on 27 Apr 2010 11:23
Hi,

I know I'm making a mistake somewhere, just not sure where.

I'm dividing 67/14 and want to get the result as 4.78 -- I'd be OK with 4.79
which is the result I'd get if the last digit was rounded up.

When I do
SELECT CAST((67/14) AS DECIMAL(3,2))

I'm getting 4.00. What am I doing wrong?
--
Thanks,

Sam
From: Plamen Ratchev on 27 Apr 2010 12:02
This is because of integer division. Try this:

SELECT 67/CAST(14 AS DECIMAL(5,2)) AS a,
67/14.0 AS b,
1.0 * 67/14 AS c,
CAST(1.0 * 67/14 AS DECIMAL(3, 2)) AS d,
ROUND(1.0 * 67/14, 2, 1) AS e;

/*

a b c d e
--------- --------- --------- ----- ----------
4.785714 4.785714 4.785714 4.79 4.780000


*/

--
Plamen Ratchev
http://www.SQLStudio.com
From: bill on 27 Apr 2010 12:13
Try this:

SELECT ROUND(CAST(67 AS DECIMAL) / CAST(14 AS DECIMAL),2)

You can get rid of the ROUND() operator if you want, or change the '2'
at the end to change the rounding precision.

Thanks,

Bill

From: Sam on 27 Apr 2010 13:01
Thank you both very much :-)
--
Thanks,

Sam


"Sam" wrote:

> Hi,
>
> I know I'm making a mistake somewhere, just not sure where.
>
> I'm dividing 67/14 and want to get the result as 4.78 -- I'd be OK with 4.79
> which is the result I'd get if the last digit was rounded up.
>
> When I do
> SELECT CAST((67/14) AS DECIMAL(3,2))
>
> I'm getting 4.00. What am I doing wrong?
> --
> Thanks,
>
> Sam
 | 
Pages: 1
Prev: Team Foundation Server and SQL Server
Next: INSERTED table no holding exact float value