Help with Round( ) function.

Hi,
I am using the round function since i want two decimal places and it doesnt
give me the correct results.
The field in which i apply it is a Real with Auto number of decimal places.
In my querries I have something like:
value1 = 2.3443 and Round(value1,2) = 2.3443 *instead of* 2.34
The funny thing is that I tried it on a copy of the database on Access XP
and I get the *same* result !!!!...
Is it because I have a fench copy of XP? I have changed my regional
settings
to English.
PLEASE HELP!
TIA
-steveTry,
declare @.value1 real
set @.value1 = 2.3443
select @.value1, round(@.value1,2), cast(round(@.value1,2) as numeric(8, 2))
AMB
"Steve" wrote:

> Hi,
> I am using the round function since i want two decimal places and it doesn
t
> give me the correct results.
> The field in which i apply it is a Real with Auto number of decimal places
.
> In my querries I have something like:
> value1 = 2.3443 and Round(value1,2) = 2.3443 *instead of* 2.34
> The funny thing is that I tried it on a copy of the database on Access XP
> and I get the *same* result !!!!...
> Is it because I have a fench copy of XP? I have changed my regional
> settings
> to English.
> PLEASE HELP!
> TIA
> -steve
>
>|||Regional settings should have nothing to do with this. I do not
think the ROUND function is giving you an incorrect answer, but
in order to help you out, you will have to post the actual statements
or a reproducible example of where you get these results. "Auto
number of decimal places" is not part of any SQL Server type, so
I'm not even sure you are using Microsoft SQL Server. If not, see
if there is a newsgroup for whatever database you are using.
Steve Kass
Drew University
Steve wrote:

>Hi,
>I am using the round function since i want two decimal places and it doesnt
>give me the correct results.
>The field in which i apply it is a Real with Auto number of decimal places.
>In my querries I have something like:
>value1 = 2.3443 and Round(value1,2) = 2.3443 *instead of* 2.34
>The funny thing is that I tried it on a copy of the database on Access XP
>and I get the *same* result !!!!...
>Is it because I have a fench copy of XP? I have changed my regional
>settings
>to English.
>PLEASE HELP!
>TIA
>-steve
>
>
>|||The problem is that you don't understand the round function, nor do you
understand numeric datatypes. Don't feel bad, they are tricky subjects.
declare @.value1 real
set @.value1 = 2.3443
select round(@.value1, 1,2)
returns:
2.2999999999999998
What you want to do is:
select round(@.value1,2) --since you don't want to truncate. This returns:
2.3399999999999999
Which in real datatype terms, is 2.34
If your goal is to get it into a precise datatype, do something like:
select cast( round(@.value1,2) as decimal(5,2))
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Steve" <try@.this.com> wrote in message
news:WWg_d.5424$sC5.53547@.weber.videotron.net...
> Hi,
> I am using the round function since i want two decimal places and it
> doesnt
> give me the correct results.
> The field in which i apply it is a Real with Auto number of decimal
> places.
> In my querries I have something like:
> value1 = 2.3443 and Round(value1,2) = 2.3443 *instead of* 2.34
> The funny thing is that I tried it on a copy of the database on Access XP
> and I get the *same* result !!!!...
> Is it because I have a fench copy of XP? I have changed my regional
> settings
> to English.
> PLEASE HELP!
> TIA
> -steve
>
>|||Steve,
WHy are you trying to round() ?
Just for display purposes ? Or do you actually want to store only two
decimal places in the database ?
If the former, Then let your UI Code control the display... SQL isn;t the
place for that..
If the Latter, just change the datatype of the column in the database to
Decimal 6,2)
(NOTE: If this Store Proc or SQL Is customized specifically for some
report, and you CAN't control the formatting in the reporting tool, then in
that case Convert the output from the real column to text
Use the STR() Function for this... Look it up in BOL
Declare @.V Real Set @.V = 2.3443
Select STR(@.V, 5,2)
"Steve" wrote:

> Hi,
> I am using the round function since i want two decimal places and it doesn
t
> give me the correct results.
> The field in which i apply it is a Real with Auto number of decimal places
.
> In my querries I have something like:
> value1 = 2.3443 and Round(value1,2) = 2.3443 *instead of* 2.34
> The funny thing is that I tried it on a copy of the database on Access XP
> and I get the *same* result !!!!...
> Is it because I have a fench copy of XP? I have changed my regional
> settings
> to English.
> PLEASE HELP!
> TIA
> -steve
>
>|||Thanx! That seems to work ( in SQL SERVER not Access, I know wrong nsgroup
but my application should target both)
I am VERY surprised. So, in SQL server, despite the fact that i have
declared (imported from Access) my val1 as real, number, whatever....
There is NO single function to output two decimal places?....
I do not want to loose precision, although not too important, i just want
to save space when i *display* the result. I want to round-off to two
decimal places.
I actually have to show 24 values ....
Thanx again!
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> a crit dans le message de
news: %23jDg$nxKFHA.2136@.TK2MSFTNGP14.phx.gbl...
> The problem is that you don't understand the round function, nor do you
> understand numeric datatypes. Don't feel bad, they are tricky subjects.
> declare @.value1 real
> set @.value1 = 2.3443
> select round(@.value1, 1,2)
> returns:
> 2.2999999999999998
> What you want to do is:
>
> select round(@.value1,2) --since you don't want to truncate. This returns:
> 2.3399999999999999
> Which in real datatype terms, is 2.34
> If your goal is to get it into a precise datatype, do something like:
> select cast( round(@.value1,2) as decimal(5,2))
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Steve" <try@.this.com> wrote in message
> news:WWg_d.5424$sC5.53547@.weber.videotron.net...
>|||On Thu, 17 Mar 2005 13:26:41 -0500, Steve wrote:

>I am VERY surprised. So, in SQL server, despite the fact that i have
>declared (imported from Access) my val1 as real, number, whatever....
>There is NO single function to output two decimal places?....
Hi Steve,
Yes, there is: CAST.
DECLARE @.Test float
SET @.Test = 12345.6789/876.54321
SELECT @.Test, CAST(@.Test AS NUMERIC(4,2))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I kind of forgot, if it is a formatting thing, you can use STR() to format
it.
declare @.value1 real
set @.value1 = 2.3443
select str(@.value1, 4,2)
Returns:
2.34
I was all caught up in the mathy angle. I would consider using the front
end to change the display, since numbers transmitted in a numeric form are
often smaller than the characters that make them up.
Sorry :)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Steve" <try@.this.com> wrote in message
news:6_j_d.6385$sC5.100192@.weber.videotron.net...
> Thanx! That seems to work ( in SQL SERVER not Access, I know wrong nsgroup
> but my application should target both)
> I am VERY surprised. So, in SQL server, despite the fact that i have
> declared (imported from Access) my val1 as real, number, whatever....
> There is NO single function to output two decimal places?....
> I do not want to loose precision, although not too important, i just want
> to save space when i *display* the result. I want to round-off to two
> decimal places.
> I actually have to show 24 values ....
> Thanx again!
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> a crit dans le message
> de news: %23jDg$nxKFHA.2136@.TK2MSFTNGP14.phx.gbl...
>