Hello, all. Tired of trying to figure out SQLs rounding scheme. If I issue
:
select (540/60)/60, I'd like to get .15 however I get 0.
How can I get the .15 result I want?
Thanks
Rozselect (540/60)/60.0
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Roz" <Roz@.discussions.microsoft.com> wrote in message
news:21B68ED2-CA86-43D8-AD1A-F970E0CD37AC@.microsoft.com...
> Hello, all. Tired of trying to figure out SQLs rounding scheme. If I iss
ue:
> select (540/60)/60, I'd like to get .15 however I get 0.
> How can I get the .15 result I want?
> Thanks
> Roz|||To get a decimal result, you have to use at least one decimal operand. When
you use integer operands, you get an integer result.
SELECT ( 540.0 / 60 ) / 60
Use CAST or CONVERT functions if you want to convert an integer datatype to
numeric/decimal datatype with required precision.
Anith|||The problem is that you haven't specified a datatype explicitly so SQL
treats 60 as an integer and you get an integer division. To avoid this use a
NUMERIC datatype for example, or include decimals to imply a NUMERIC:
SELECT (540.0/60.0)/60.0
David Portas
SQL Server MVP
--|||Folks,
Thanks. These all worked beautifully!!!!
Roz
"David Portas" wrote:
> The problem is that you haven't specified a datatype explicitly so SQL
> treats 60 as an integer and you get an integer division. To avoid this use
a
> NUMERIC datatype for example, or include decimals to imply a NUMERIC:
> SELECT (540.0/60.0)/60.0
> --
> David Portas
> SQL Server MVP
> --
>
>|||Try this...
SELECT RTRIM((CONVERT(FLOAT(2),
540)/CONVERT(FLOAT(2),60))/CONVERT(FLOAT(2),60))
Regards
Sivakumar
"Roz" wrote:
> Hello, all. Tired of trying to figure out SQLs rounding scheme. If I iss
ue:
> select (540/60)/60, I'd like to get .15 however I get 0.
> How can I get the .15 result I want?
> Thanks
> Roz
Showing posts with label rounding. Show all posts
Showing posts with label rounding. Show all posts
Wednesday, March 7, 2012
Rounding very small negative numbers to string
Hi,
I'm trying to round a very small negative number, e.g. -.024, to a string.
If I round -.024 to 1 place, I get 0.0
However, when converting to string, I get .-0
Is there some combination of functions to get rid of the minus sign in such
a situation, or do I have to put in an IF condition to check if number < .05
and change it to ABS?
Below is code that is resulting in -0.
Thanks.
Alan
DECLARE @.Value float(53)
DECLARE @.ValueRounded float(53)
SET @.Value = -.024 -- Any negative number < .05
SELECT @.Value
SET @.ValueRounded = ROUND(@.Value, 1)
SELECT @.ValueRounded
SELECT CONVERT(CHAR(6), @.ValueRounded)What about this?
DECLARE @.Value float(53)
DECLARE @.ValueRounded decimal(6,1)--convert to decimal
SET @.Value = -.024 -- Any negative number < .05
SELECT @.Value
SET @.ValueRounded = ROUND(@.Value, 1)
SELECT @.ValueRounded
SELECT CONVERT(CHAR(6), @.ValueRounded)
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Alan Z. Scharf wrote:
> Hi,
> I'm trying to round a very small negative number, e.g. -.024, to a string.
> If I round -.024 to 1 place, I get 0.0
> However, when converting to string, I get .-0
> Is there some combination of functions to get rid of the minus sign in suc
h
> a situation, or do I have to put in an IF condition to check if number < .
05
> and change it to ABS?
> Below is code that is resulting in -0.
> Thanks.
> Alan
>
> DECLARE @.Value float(53)
> DECLARE @.ValueRounded float(53)
> SET @.Value = -.024 -- Any negative number < .05
> SELECT @.Value
> SET @.ValueRounded = ROUND(@.Value, 1)
> SELECT @.ValueRounded
> SELECT CONVERT(CHAR(6), @.ValueRounded)|||Menace,
Thanks very much. I figured there must be a way.
Alan
"SQL Menace" <denis.gobo@.gmail.com> wrote in message
news:1149092714.650915.239590@.j55g2000cwa.googlegroups.com...
> What about this?
>
> DECLARE @.Value float(53)
> DECLARE @.ValueRounded decimal(6,1)--convert to decimal
> SET @.Value = -.024 -- Any negative number < .05
> SELECT @.Value
> SET @.ValueRounded = ROUND(@.Value, 1)
> SELECT @.ValueRounded
> SELECT CONVERT(CHAR(6), @.ValueRounded)
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
> Alan Z. Scharf wrote:
string.
such
.05
>|||Is this what u need?
DECLARE @.Value float(53)
SET @.Value = -.025
SELECT ABS(Floor(@.Value))
Regards
Sudarshan Selvaraja
"Alan Z. Scharf" wrote:
> Hi,
> I'm trying to round a very small negative number, e.g. -.024, to a string.
> If I round -.024 to 1 place, I get 0.0
> However, when converting to string, I get .-0
> Is there some combination of functions to get rid of the minus sign in suc
h
> a situation, or do I have to put in an IF condition to check if number < .
05
> and change it to ABS?
> Below is code that is resulting in -0.
> Thanks.
> Alan
>
> DECLARE @.Value float(53)
> DECLARE @.ValueRounded float(53)
> SET @.Value = -.024 -- Any negative number < .05
> SELECT @.Value
> SET @.ValueRounded = ROUND(@.Value, 1)
> SELECT @.ValueRounded
> SELECT CONVERT(CHAR(6), @.ValueRounded)
>
>
>|||BTW, FLOAT is an approximate representation. You would probably be better
off representing your data as a NUMERIC type.
"Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
news:Od%23oc2MhGHA.3756@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I'm trying to round a very small negative number, e.g. -.024, to a string.
> If I round -.024 to 1 place, I get 0.0
> However, when converting to string, I get .-0
> Is there some combination of functions to get rid of the minus sign in
> such
> a situation, or do I have to put in an IF condition to check if number <
> .05
> and change it to ABS?
> Below is code that is resulting in -0.
> Thanks.
> Alan
>
> DECLARE @.Value float(53)
> DECLARE @.ValueRounded float(53)
> SET @.Value = -.024 -- Any negative number < .05
> SELECT @.Value
> SET @.ValueRounded = ROUND(@.Value, 1)
> SELECT @.ValueRounded
> SELECT CONVERT(CHAR(6), @.ValueRounded)
>
>
>|||>> I'm trying to round a very small negative number, e.g. -.024, to a string
. <<
Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This a more basic programming principle than just SQL
and RDBMS.
I'm trying to round a very small negative number, e.g. -.024, to a string.
If I round -.024 to 1 place, I get 0.0
However, when converting to string, I get .-0
Is there some combination of functions to get rid of the minus sign in such
a situation, or do I have to put in an IF condition to check if number < .05
and change it to ABS?
Below is code that is resulting in -0.
Thanks.
Alan
DECLARE @.Value float(53)
DECLARE @.ValueRounded float(53)
SET @.Value = -.024 -- Any negative number < .05
SELECT @.Value
SET @.ValueRounded = ROUND(@.Value, 1)
SELECT @.ValueRounded
SELECT CONVERT(CHAR(6), @.ValueRounded)What about this?
DECLARE @.Value float(53)
DECLARE @.ValueRounded decimal(6,1)--convert to decimal
SET @.Value = -.024 -- Any negative number < .05
SELECT @.Value
SET @.ValueRounded = ROUND(@.Value, 1)
SELECT @.ValueRounded
SELECT CONVERT(CHAR(6), @.ValueRounded)
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Alan Z. Scharf wrote:
> Hi,
> I'm trying to round a very small negative number, e.g. -.024, to a string.
> If I round -.024 to 1 place, I get 0.0
> However, when converting to string, I get .-0
> Is there some combination of functions to get rid of the minus sign in suc
h
> a situation, or do I have to put in an IF condition to check if number < .
05
> and change it to ABS?
> Below is code that is resulting in -0.
> Thanks.
> Alan
>
> DECLARE @.Value float(53)
> DECLARE @.ValueRounded float(53)
> SET @.Value = -.024 -- Any negative number < .05
> SELECT @.Value
> SET @.ValueRounded = ROUND(@.Value, 1)
> SELECT @.ValueRounded
> SELECT CONVERT(CHAR(6), @.ValueRounded)|||Menace,
Thanks very much. I figured there must be a way.
Alan
"SQL Menace" <denis.gobo@.gmail.com> wrote in message
news:1149092714.650915.239590@.j55g2000cwa.googlegroups.com...
> What about this?
>
> DECLARE @.Value float(53)
> DECLARE @.ValueRounded decimal(6,1)--convert to decimal
> SET @.Value = -.024 -- Any negative number < .05
> SELECT @.Value
> SET @.ValueRounded = ROUND(@.Value, 1)
> SELECT @.ValueRounded
> SELECT CONVERT(CHAR(6), @.ValueRounded)
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
> Alan Z. Scharf wrote:
string.
such
.05
>|||Is this what u need?
DECLARE @.Value float(53)
SET @.Value = -.025
SELECT ABS(Floor(@.Value))
Regards
Sudarshan Selvaraja
"Alan Z. Scharf" wrote:
> Hi,
> I'm trying to round a very small negative number, e.g. -.024, to a string.
> If I round -.024 to 1 place, I get 0.0
> However, when converting to string, I get .-0
> Is there some combination of functions to get rid of the minus sign in suc
h
> a situation, or do I have to put in an IF condition to check if number < .
05
> and change it to ABS?
> Below is code that is resulting in -0.
> Thanks.
> Alan
>
> DECLARE @.Value float(53)
> DECLARE @.ValueRounded float(53)
> SET @.Value = -.024 -- Any negative number < .05
> SELECT @.Value
> SET @.ValueRounded = ROUND(@.Value, 1)
> SELECT @.ValueRounded
> SELECT CONVERT(CHAR(6), @.ValueRounded)
>
>
>|||BTW, FLOAT is an approximate representation. You would probably be better
off representing your data as a NUMERIC type.
"Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
news:Od%23oc2MhGHA.3756@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I'm trying to round a very small negative number, e.g. -.024, to a string.
> If I round -.024 to 1 place, I get 0.0
> However, when converting to string, I get .-0
> Is there some combination of functions to get rid of the minus sign in
> such
> a situation, or do I have to put in an IF condition to check if number <
> .05
> and change it to ABS?
> Below is code that is resulting in -0.
> Thanks.
> Alan
>
> DECLARE @.Value float(53)
> DECLARE @.ValueRounded float(53)
> SET @.Value = -.024 -- Any negative number < .05
> SELECT @.Value
> SET @.ValueRounded = ROUND(@.Value, 1)
> SELECT @.ValueRounded
> SELECT CONVERT(CHAR(6), @.ValueRounded)
>
>
>|||>> I'm trying to round a very small negative number, e.g. -.024, to a string
. <<
Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This a more basic programming principle than just SQL
and RDBMS.
Rounding Values
Hi,
How can I round a value to the next int number like all values > 1 and < 2 I need to round to 2 and on and on...to all numbers
So If I have 2.1 it's 3 if I have 2.9 it's 3 ...and so on...
Thanks
There is a built-in function: CEILING().|||Thanks.. It workedRounding up+
I need to round numbers.
it can be 1.1 or 1.2 or 1.9 it doesn't matter it always need to round up to whole number
it can be 1.1 or 1.2 or 1.9 it doesn't matter it always need to round up to whole number
select ceiling (1.1)
select ceiling (1.2)
select ceiling (1.9)
|||thank youRounding Up
I have the following code that retreives the current value of the item price. however it always rounds up. If I manually enter a return value like so:
return (decimal)12.47
It returns the correct value, however if I set it with an expression like this:
return(decimal)arParam[1].Value;
It rounds the number up: How can I get it to not round up when insertign a value based ona expression?
http://forums.asp.net/887067/ShowPost.aspx
return (decimal)12.47
It returns the correct value, however if I set it with an expression like this:
return(decimal)arParam[1].Value;
It rounds the number up: How can I get it to not round up when insertign a value based ona expression?
publicdecimal GetCreditPrice(string CustomerSecurityKey)
{
try
{
System.Data.SqlClient.SqlParameter prmCrnt;
System.Data.SqlClient.SqlParameter[] arParam =new System.Data.SqlClient.SqlParameter[2];
prmCrnt =new System.Data.SqlClient.SqlParameter("@.CustomerSecurityKey", SqlDbType.VarChar,25);
prmCrnt.Value = CustomerSecurityKey;
arParam[0] = prmCrnt;
prmCrnt =new System.Data.SqlClient.SqlParameter("@.Price", SqlDbType.Decimal);
prmCrnt.Direction = ParameterDirection.Output;
arParam[1] = prmCrnt;
SqlHelper.ExecuteNonQuery(stConnection, CommandType.StoredProcedure, "GetCreditPrice", arParam);
return(decimal)arParam[1].Value;
}
catch(System.Exception ex)
{
throw ex;
}
}
Try the link I posted in this post for custom String Formatting. Hope this helps.http://forums.asp.net/887067/ShowPost.aspx
Rounding to the nearest thousand
Hi
Which parameter value for the Round function do I need to pass to get it to round to the nearest thousand ?
Thanks,
Neil
Hello Neil,
Try this:
=Round(Fields!Field1.Value, 3)
Hope this helps.
Jarret
|||Sorry, I mis-read your question. I thought it said thousandth.
This should do what you want:
=cInt(Fields!Field1.Value / 1000) * 1000
Jarret
|||For formatting it is
#,###,.
|||Thanks Jarret, Just what I needed..Thanks!!
|||Thanks Ewild!
You read my mind!! Also just what I needed
Rounding seconds up to the nearest 15 minutes
I have a field with seconds in it and I need to disply it in hours which I can do by dividing it by 3600, but I am trying to figure out how to round it up to the nearest 15 minutes. I have tried a couple of things with ROUND and CEILING, but am not getting the right numbers back. Any help would be greatly appreciated.
Hi,You need to divide the seconds by 15 first, round that (up or down as your logic dictates) and then divide by 4 to get hours.
Try this as example:
DECLARE @.secondsint
SET @.seconds = 1632DECLARE @.hoursdecimal(18,2)
SET @.hours = CEILING(convert(decimal,@.seconds)/15 )SELECT @.hours, @.hours/4
The result should be 109, 27.25 (27 and a quarter hours).
I included the two results so you can see what is happening.
Change the seconds to 1640 and the figures are 110, 27.50 (27 and half hours)
Okay, you totally lost me. I only get to dabble with mssql every few months so please excuse my ignorance. I am trying to work what you said into my query, but I when I put in the decimal it throws an error.
SELECT dbo.SLPTRANS.ClientID,SUM(dbo.SLPTRANS.TransValue)AS Expr1,SUM(CEILING(dbo.SLPTRANS.TimeSpent / 15) * dbo.SLPTRANS.RateValue)AS BillableFeesFROM dbo.SLPTRANSINNERJOIN dbo.INVOICEON dbo.SLPTRANS.InvoiceID = dbo.INVOICE.RecordIDGROUP BY dbo.SLPTRANS.ClientIDHAVING (dbo.SLPTRANS.ClientID = 405)|||
Hi,
Apologies for the late reply!
I think the SUM should be:
SUM( (CEILING(db.SLPTRANS.TimeSpent /15)/4 ) * dbo.SLPTRANS.RateValue)
What error message do you get?
Rounding real number
Dear All,
I've a problem when input real number. If I key in 6.5, on query
analyzer, the record show 6.499999999 and so.
Does anybody known the solution?
Thanks very much
MichaelDon't use float or real. These are "approximate" numerical datatype (when wo
rking with 10 base
systems as we humans tend to do). Use numeric or decimal instead.
http://www.aspfaq.com/show.asp?id=2477
http://www.aspfaq.com/show.asp?id=2503
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Yap Michael" <yapmichael2000@.gmail.com> wrote in message
news:OJyzGck%23FHA.3136@.TK2MSFTNGP15.phx.gbl...
> Dear All,
> I've a problem when input real number. If I key in 6.5, on query analyzer,
the record show
> 6.499999999 and so.
> Does anybody known the solution?
> Thanks very much
> Michael|||Yap
What is your SQL Server's version
DECLARE @.d DECIMAL(18,1),@.w REAL
SET @.d=6.5
SET @.w=6.5
SELECT @.d,@.w
"Yap Michael" <yapmichael2000@.gmail.com> wrote in message
news:OJyzGck%23FHA.3136@.TK2MSFTNGP15.phx.gbl...
> Dear All,
> I've a problem when input real number. If I key in 6.5, on query analyzer,
> the record show 6.499999999 and so.
> Does anybody known the solution?
> Thanks very much
> Michael
I've a problem when input real number. If I key in 6.5, on query
analyzer, the record show 6.499999999 and so.
Does anybody known the solution?
Thanks very much
MichaelDon't use float or real. These are "approximate" numerical datatype (when wo
rking with 10 base
systems as we humans tend to do). Use numeric or decimal instead.
http://www.aspfaq.com/show.asp?id=2477
http://www.aspfaq.com/show.asp?id=2503
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Yap Michael" <yapmichael2000@.gmail.com> wrote in message
news:OJyzGck%23FHA.3136@.TK2MSFTNGP15.phx.gbl...
> Dear All,
> I've a problem when input real number. If I key in 6.5, on query analyzer,
the record show
> 6.499999999 and so.
> Does anybody known the solution?
> Thanks very much
> Michael|||Yap
What is your SQL Server's version
DECLARE @.d DECIMAL(18,1),@.w REAL
SET @.d=6.5
SET @.w=6.5
SELECT @.d,@.w
"Yap Michael" <yapmichael2000@.gmail.com> wrote in message
news:OJyzGck%23FHA.3136@.TK2MSFTNGP15.phx.gbl...
> Dear All,
> I've a problem when input real number. If I key in 6.5, on query analyzer,
> the record show 6.499999999 and so.
> Does anybody known the solution?
> Thanks very much
> Michael
Rounding real number
Dear All,
I've a problem when input real number. If I key in 6.5, on query
analyzer, the record show 6.499999999 and so.
Does anybody known the solution?
Thanks very much
Michael
Don't use float or real. These are "approximate" numerical datatype (when working with 10 base
systems as we humans tend to do). Use numeric or decimal instead.
http://www.aspfaq.com/show.asp?id=2477
http://www.aspfaq.com/show.asp?id=2503
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Yap Michael" <yapmichael2000@.gmail.com> wrote in message
news:OJyzGck%23FHA.3136@.TK2MSFTNGP15.phx.gbl...
> Dear All,
> I've a problem when input real number. If I key in 6.5, on query analyzer, the record show
> 6.499999999 and so.
> Does anybody known the solution?
> Thanks very much
> Michael
|||Yap
What is your SQL Server's version
DECLARE @.d DECIMAL(18,1),@.w REAL
SET @.d=6.5
SET @.w=6.5
SELECT @.d,@.w
"Yap Michael" <yapmichael2000@.gmail.com> wrote in message
news:OJyzGck%23FHA.3136@.TK2MSFTNGP15.phx.gbl...
> Dear All,
> I've a problem when input real number. If I key in 6.5, on query analyzer,
> the record show 6.499999999 and so.
> Does anybody known the solution?
> Thanks very much
> Michael
I've a problem when input real number. If I key in 6.5, on query
analyzer, the record show 6.499999999 and so.
Does anybody known the solution?
Thanks very much
Michael
Don't use float or real. These are "approximate" numerical datatype (when working with 10 base
systems as we humans tend to do). Use numeric or decimal instead.
http://www.aspfaq.com/show.asp?id=2477
http://www.aspfaq.com/show.asp?id=2503
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Yap Michael" <yapmichael2000@.gmail.com> wrote in message
news:OJyzGck%23FHA.3136@.TK2MSFTNGP15.phx.gbl...
> Dear All,
> I've a problem when input real number. If I key in 6.5, on query analyzer, the record show
> 6.499999999 and so.
> Does anybody known the solution?
> Thanks very much
> Michael
|||Yap
What is your SQL Server's version
DECLARE @.d DECIMAL(18,1),@.w REAL
SET @.d=6.5
SET @.w=6.5
SELECT @.d,@.w
"Yap Michael" <yapmichael2000@.gmail.com> wrote in message
news:OJyzGck%23FHA.3136@.TK2MSFTNGP15.phx.gbl...
> Dear All,
> I've a problem when input real number. If I key in 6.5, on query analyzer,
> the record show 6.499999999 and so.
> Does anybody known the solution?
> Thanks very much
> Michael
Rounding real number
Dear All,
I've a problem when input real number. If I key in 6.5, on query
analyzer, the record show 6.499999999 and so.
Does anybody known the solution?
Thanks very much
MichaelDon't use float or real. These are "approximate" numerical datatype (when working with 10 base
systems as we humans tend to do). Use numeric or decimal instead.
http://www.aspfaq.com/show.asp?id=2477
http://www.aspfaq.com/show.asp?id=2503
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Yap Michael" <yapmichael2000@.gmail.com> wrote in message
news:OJyzGck%23FHA.3136@.TK2MSFTNGP15.phx.gbl...
> Dear All,
> I've a problem when input real number. If I key in 6.5, on query analyzer, the record show
> 6.499999999 and so.
> Does anybody known the solution?
> Thanks very much
> Michael|||Yap
What is your SQL Server's version
DECLARE @.d DECIMAL(18,1),@.w REAL
SET @.d=6.5
SET @.w=6.5
SELECT @.d,@.w
"Yap Michael" <yapmichael2000@.gmail.com> wrote in message
news:OJyzGck%23FHA.3136@.TK2MSFTNGP15.phx.gbl...
> Dear All,
> I've a problem when input real number. If I key in 6.5, on query analyzer,
> the record show 6.499999999 and so.
> Does anybody known the solution?
> Thanks very much
> Michael
I've a problem when input real number. If I key in 6.5, on query
analyzer, the record show 6.499999999 and so.
Does anybody known the solution?
Thanks very much
MichaelDon't use float or real. These are "approximate" numerical datatype (when working with 10 base
systems as we humans tend to do). Use numeric or decimal instead.
http://www.aspfaq.com/show.asp?id=2477
http://www.aspfaq.com/show.asp?id=2503
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Yap Michael" <yapmichael2000@.gmail.com> wrote in message
news:OJyzGck%23FHA.3136@.TK2MSFTNGP15.phx.gbl...
> Dear All,
> I've a problem when input real number. If I key in 6.5, on query analyzer, the record show
> 6.499999999 and so.
> Does anybody known the solution?
> Thanks very much
> Michael|||Yap
What is your SQL Server's version
DECLARE @.d DECIMAL(18,1),@.w REAL
SET @.d=6.5
SET @.w=6.5
SELECT @.d,@.w
"Yap Michael" <yapmichael2000@.gmail.com> wrote in message
news:OJyzGck%23FHA.3136@.TK2MSFTNGP15.phx.gbl...
> Dear All,
> I've a problem when input real number. If I key in 6.5, on query analyzer,
> the record show 6.499999999 and so.
> Does anybody known the solution?
> Thanks very much
> Michael
Rounding problems performing math with Decimal data types
We're seeing some rounding errors in a financial application. We had chosen
to use Decimal(38, 10) fields for both dollar amounts and allocation
percentages. However, when we multiply the numbers together we loose
significant precision, as in the following example:
DECLARE @.TNA_PERCENT AS DECIMAL(38, 10)
DECLARE @.ALLOC_PERCENT AS DECIMAL(38, 10)
DECLARE @.Result AS DECIMAL(38, 10)
SET @.TNA_PERCENT = .7363612218
SET @.ALLOC_PERCENT = .01
SET @.Result = @.TNA_PERCENT * @.ALLOC_PERCENT
SELECT @.TNA_PERCENT, @.ALLOC_PERCENT, @.Result
Returns:
.7363612218 .0100000000 .0073640000
Granted, the Decimal(38, 10) data type is overkill for the value .01, but it
was one example that came up. Why do we lose the precision during this
multiplication operation? Should we revise our data types, and if so, to
what? We will be dealing with large dollar amounts that need to be multiplie
d
accurately by calculated allocation percentages and stored.
Thanks,
Craig RandallCraig,
If you multiply a decimal 38 with another decimal 38, then the result
would be a decimal 76 (38+38). Since your result variable is restricted
to 38 digits, you loose precision.
If you want an exact result in a decimal(38,10), then the two
multiplication variables should have a combined total of at most 38.
Same for the precision. So if you multiply a decimal(19,5) with a
decimal(19,5), then this will fit in a decimal(38,10).
You will see that if you change the definition of @.TNA_PERCENT and
@.ALLOC_PERCENT to Decimal(19,10) that the result will be:
.7363612218 .0100000000 .0073636122
In this case, to get an exact result, you would need a precision of (at
least) 12 digits.
Gert-Jan
Crandaddy wrote:
> We're seeing some rounding errors in a financial application. We had chose
n
> to use Decimal(38, 10) fields for both dollar amounts and allocation
> percentages. However, when we multiply the numbers together we loose
> significant precision, as in the following example:
> DECLARE @.TNA_PERCENT AS DECIMAL(38, 10)
> DECLARE @.ALLOC_PERCENT AS DECIMAL(38, 10)
> DECLARE @.Result AS DECIMAL(38, 10)
> SET @.TNA_PERCENT = .7363612218
> SET @.ALLOC_PERCENT = .01
> SET @.Result = @.TNA_PERCENT * @.ALLOC_PERCENT
> SELECT @.TNA_PERCENT, @.ALLOC_PERCENT, @.Result
> Returns:
> .7363612218 .0100000000 .0073640000
> Granted, the Decimal(38, 10) data type is overkill for the value .01, but
it
> was one example that came up. Why do we lose the precision during this
> multiplication operation? Should we revise our data types, and if so, to
> what? We will be dealing with large dollar amounts that need to be multipl
ied
> accurately by calculated allocation percentages and stored.
> Thanks,
> Craig Randall
to use Decimal(38, 10) fields for both dollar amounts and allocation
percentages. However, when we multiply the numbers together we loose
significant precision, as in the following example:
DECLARE @.TNA_PERCENT AS DECIMAL(38, 10)
DECLARE @.ALLOC_PERCENT AS DECIMAL(38, 10)
DECLARE @.Result AS DECIMAL(38, 10)
SET @.TNA_PERCENT = .7363612218
SET @.ALLOC_PERCENT = .01
SET @.Result = @.TNA_PERCENT * @.ALLOC_PERCENT
SELECT @.TNA_PERCENT, @.ALLOC_PERCENT, @.Result
Returns:
.7363612218 .0100000000 .0073640000
Granted, the Decimal(38, 10) data type is overkill for the value .01, but it
was one example that came up. Why do we lose the precision during this
multiplication operation? Should we revise our data types, and if so, to
what? We will be dealing with large dollar amounts that need to be multiplie
d
accurately by calculated allocation percentages and stored.
Thanks,
Craig RandallCraig,
If you multiply a decimal 38 with another decimal 38, then the result
would be a decimal 76 (38+38). Since your result variable is restricted
to 38 digits, you loose precision.
If you want an exact result in a decimal(38,10), then the two
multiplication variables should have a combined total of at most 38.
Same for the precision. So if you multiply a decimal(19,5) with a
decimal(19,5), then this will fit in a decimal(38,10).
You will see that if you change the definition of @.TNA_PERCENT and
@.ALLOC_PERCENT to Decimal(19,10) that the result will be:
.7363612218 .0100000000 .0073636122
In this case, to get an exact result, you would need a precision of (at
least) 12 digits.
Gert-Jan
Crandaddy wrote:
> We're seeing some rounding errors in a financial application. We had chose
n
> to use Decimal(38, 10) fields for both dollar amounts and allocation
> percentages. However, when we multiply the numbers together we loose
> significant precision, as in the following example:
> DECLARE @.TNA_PERCENT AS DECIMAL(38, 10)
> DECLARE @.ALLOC_PERCENT AS DECIMAL(38, 10)
> DECLARE @.Result AS DECIMAL(38, 10)
> SET @.TNA_PERCENT = .7363612218
> SET @.ALLOC_PERCENT = .01
> SET @.Result = @.TNA_PERCENT * @.ALLOC_PERCENT
> SELECT @.TNA_PERCENT, @.ALLOC_PERCENT, @.Result
> Returns:
> .7363612218 .0100000000 .0073640000
> Granted, the Decimal(38, 10) data type is overkill for the value .01, but
it
> was one example that came up. Why do we lose the precision during this
> multiplication operation? Should we revise our data types, and if so, to
> what? We will be dealing with large dollar amounts that need to be multipl
ied
> accurately by calculated allocation percentages and stored.
> Thanks,
> Craig Randall
Rounding problems
I have rounding problems when editing or inserting a new record in float type fields.
e.g. I have a cursor running an agrregate SQL statement. I have a calculated field Sum(DFactor*Cost). DFactor gets values -1,1 and values of Cost in the table have 2 digits. I get these values in a variable e.g. @.FCost. Then I round @.FCost=Round(@.FCost,2).
When I try to inert this value to a new record again I'using Round(@.FCost,2).
However in a lot of records a lot of digits are stored.
I have the same probelm when trying to insert values from MSAccess by ODBC. Although I'm using CLng(@.FCost*100)/100 in order to have 2 digits, a lot of demical values are created.
What is the best practise in order to solve this problem?
Regards,
ManolisIf you are using a FLOAT column to store data of type MONEY, that's a problem. If you are using a FLOAT column to store data of type DECIMAL (x, 2), that's also a problem. Is your underlying problem one of datatype, not actually rounding?
-PatP|||Although I'm using CLng(@.FCost*100)/100 in order to have 2 digits, a lot of demical values are created.The result will have decimals. You need this: CLng(@.FCost*100/100)
e.g. I have a cursor running an agrregate SQL statement. I have a calculated field Sum(DFactor*Cost). DFactor gets values -1,1 and values of Cost in the table have 2 digits. I get these values in a variable e.g. @.FCost. Then I round @.FCost=Round(@.FCost,2).
When I try to inert this value to a new record again I'using Round(@.FCost,2).
However in a lot of records a lot of digits are stored.
I have the same probelm when trying to insert values from MSAccess by ODBC. Although I'm using CLng(@.FCost*100)/100 in order to have 2 digits, a lot of demical values are created.
What is the best practise in order to solve this problem?
Regards,
ManolisIf you are using a FLOAT column to store data of type MONEY, that's a problem. If you are using a FLOAT column to store data of type DECIMAL (x, 2), that's also a problem. Is your underlying problem one of datatype, not actually rounding?
-PatP|||Although I'm using CLng(@.FCost*100)/100 in order to have 2 digits, a lot of demical values are created.The result will have decimals. You need this: CLng(@.FCost*100/100)
Rounding problem with data conversion
Hi,
I am trying to convert a char column so that I can subtract the values from
another column. I have tried cast and convert, to change it to decimal, but
have found that both methods round values to the nearest whole number.
The column contains money, so this is causing me to lose the pence.
Any help would be greatly appreciated.
Many thanks
PaulI've managed to solve this now, so will close the thread.
The convert worked in the end.
Thanks anyway.
"PaulGodfrey" wrote:
> Hi,
> I am trying to convert a char column so that I can subtract the values fro
m
> another column. I have tried cast and convert, to change it to decimal, b
ut
> have found that both methods round values to the nearest whole number.
> The column contains money, so this is causing me to lose the pence.
> Any help would be greatly appreciated.
> Many thanks
> Paul
I am trying to convert a char column so that I can subtract the values from
another column. I have tried cast and convert, to change it to decimal, but
have found that both methods round values to the nearest whole number.
The column contains money, so this is causing me to lose the pence.
Any help would be greatly appreciated.
Many thanks
PaulI've managed to solve this now, so will close the thread.
The convert worked in the end.
Thanks anyway.
"PaulGodfrey" wrote:
> Hi,
> I am trying to convert a char column so that I can subtract the values fro
m
> another column. I have tried cast and convert, to change it to decimal, b
ut
> have found that both methods round values to the nearest whole number.
> The column contains money, so this is causing me to lose the pence.
> Any help would be greatly appreciated.
> Many thanks
> Paul
Rounding Problem in total
In a cell I have the following:
=iif(Parameters!VOLREV.Value="Volume",sum(math.Round(Fields!ADMINSRV_SLS.Value)),sum(math.Round(Fields!ADMINSRV_FEES.Value)))
Depending on parameter I am either rounding the sum of sales or the sum of fees. This works beautifully. Problem is when I copy this to the total row it is not the total of the column. They are slightly off. I even tried naming the cell and then creating a calculated field in the total line to make sure that I am adding the values of that cell. =sum(ReportItems!MyNamedCell.value) But this crashes VS everytime and it has to restart. Does anyone have a suggestion please.
In the total cell, is the expression rounding and then summing the values, like in the data cell? If so, this should produce identical results. How off are the values?Also, the other issue, regarding the use of the expression, =sum(ReportItems!MyNamedCell.Value), should just cause a publishing error. ReportItems can not be used inside aggregates unless the aggregate is used in the page header or footer. I'm not sure why it is crashing VS.|||I am wondering if it is summing and then rounding because the totals are slightly off of what the total of the columns should be.|||
Found the answer. For whatever reason in my grouping lines that show my detail this works:
=iif(Parameters!VOLREV.Value="Volume",math.Round(sum(Fields!ADMINSRV_SLS.Value)),math.Round(sum(Fields!ADMINSRV_FEES.Value)))
But in my total line I had to reverse the logic to this.
=iif(Parameters!VOLREV.Value="Volume",sum(math.Round(Fields!ADMINSRV_SLS.Value)),sum(math.Round(Fields!ADMINSRV_FEES.Value)))
Rounding Problem
Hi all,
Is there any function that truncates a decimal numeric without rounding, if
i use cast it rounds the figure but can i don't want that i.e i want it to
be as follows:
23.1238 turns to 23.123 and not 23.124 so it keeps only three decimals as
is
Please advise if there is any function or so
Thanks in advance
Suad
Try
Round(23.1238,3,1)
Is there any function that truncates a decimal numeric without rounding, if
i use cast it rounds the figure but can i don't want that i.e i want it to
be as follows:
23.1238 turns to 23.123 and not 23.124 so it keeps only three decimals as
is
Please advise if there is any function or so
Thanks in advance
Suad
Try
Round(23.1238,3,1)
Rounding problem
I have a table with a field called "Sell" which is:
Decimal 9, precision=10, scale=2
When I do: Select sum(sell) from Table, I get the answer: 2,568.78, but
when I add up the numbers on a calculator I get: 2,568.72, off by 6 cents.
Needless to say, this drives accountants nutty. Changing the data type isn't
an option since this a commercial software product we're using (the
developers know about the problem, but choose to let their reports be off by
pennies). We'd like an accurate Sum() amount, and we've tried doing
Round(@.SumSell,2) but that doesn't work.
Any ideas? THanks!I've been messing with this a little to try to duplicate your problem, but
can not..
Can you post a create table, and insert statements, and a select statement
which repros the problem?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23GaANOiXEHA.2844@.TK2MSFTNGP11.phx.gbl...
> I have a table with a field called "Sell" which is:
> Decimal 9, precision=10, scale=2
> When I do: Select sum(sell) from Table, I get the answer: 2,568.78, but
> when I add up the numbers on a calculator I get: 2,568.72, off by 6 cents.
> Needless to say, this drives accountants nutty. Changing the data type
isn't
> an option since this a commercial software product we're using (the
> developers know about the problem, but choose to let their reports be off
by
> pennies). We'd like an accurate Sum() amount, and we've tried doing
> Round(@.SumSell,2) but that doesn't work.
> Any ideas? THanks!
>
Decimal 9, precision=10, scale=2
When I do: Select sum(sell) from Table, I get the answer: 2,568.78, but
when I add up the numbers on a calculator I get: 2,568.72, off by 6 cents.
Needless to say, this drives accountants nutty. Changing the data type isn't
an option since this a commercial software product we're using (the
developers know about the problem, but choose to let their reports be off by
pennies). We'd like an accurate Sum() amount, and we've tried doing
Round(@.SumSell,2) but that doesn't work.
Any ideas? THanks!I've been messing with this a little to try to duplicate your problem, but
can not..
Can you post a create table, and insert statements, and a select statement
which repros the problem?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23GaANOiXEHA.2844@.TK2MSFTNGP11.phx.gbl...
> I have a table with a field called "Sell" which is:
> Decimal 9, precision=10, scale=2
> When I do: Select sum(sell) from Table, I get the answer: 2,568.78, but
> when I add up the numbers on a calculator I get: 2,568.72, off by 6 cents.
> Needless to say, this drives accountants nutty. Changing the data type
isn't
> an option since this a commercial software product we're using (the
> developers know about the problem, but choose to let their reports be off
by
> pennies). We'd like an accurate Sum() amount, and we've tried doing
> Round(@.SumSell,2) but that doesn't work.
> Any ideas? THanks!
>
Rounding problem
I have a table with a field called "Sell" which is:
Decimal 9, precision=10, scale=2
When I do: Select sum(sell) from Table, I get the answer: 2,568.78, but
when I add up the numbers on a calculator I get: 2,568.72, off by 6 cents.
Needless to say, this drives accountants nutty. Changing the data type isn't
an option since this a commercial software product we're using (the
developers know about the problem, but choose to let their reports be off by
pennies). We'd like an accurate Sum() amount, and we've tried doing
Round(@.SumSell,2) but that doesn't work.
Any ideas? THanks!
I've been messing with this a little to try to duplicate your problem, but
can not..
Can you post a create table, and insert statements, and a select statement
which repros the problem?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23GaANOiXEHA.2844@.TK2MSFTNGP11.phx.gbl...
> I have a table with a field called "Sell" which is:
> Decimal 9, precision=10, scale=2
> When I do: Select sum(sell) from Table, I get the answer: 2,568.78, but
> when I add up the numbers on a calculator I get: 2,568.72, off by 6 cents.
> Needless to say, this drives accountants nutty. Changing the data type
isn't
> an option since this a commercial software product we're using (the
> developers know about the problem, but choose to let their reports be off
by
> pennies). We'd like an accurate Sum() amount, and we've tried doing
> Round(@.SumSell,2) but that doesn't work.
> Any ideas? THanks!
>
Decimal 9, precision=10, scale=2
When I do: Select sum(sell) from Table, I get the answer: 2,568.78, but
when I add up the numbers on a calculator I get: 2,568.72, off by 6 cents.
Needless to say, this drives accountants nutty. Changing the data type isn't
an option since this a commercial software product we're using (the
developers know about the problem, but choose to let their reports be off by
pennies). We'd like an accurate Sum() amount, and we've tried doing
Round(@.SumSell,2) but that doesn't work.
Any ideas? THanks!
I've been messing with this a little to try to duplicate your problem, but
can not..
Can you post a create table, and insert statements, and a select statement
which repros the problem?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23GaANOiXEHA.2844@.TK2MSFTNGP11.phx.gbl...
> I have a table with a field called "Sell" which is:
> Decimal 9, precision=10, scale=2
> When I do: Select sum(sell) from Table, I get the answer: 2,568.78, but
> when I add up the numbers on a calculator I get: 2,568.72, off by 6 cents.
> Needless to say, this drives accountants nutty. Changing the data type
isn't
> an option since this a commercial software product we're using (the
> developers know about the problem, but choose to let their reports be off
by
> pennies). We'd like an accurate Sum() amount, and we've tried doing
> Round(@.SumSell,2) but that doesn't work.
> Any ideas? THanks!
>
Rounding problem
I have a table with a field called "Sell" which is:
Decimal 9, precision=10, scale=2
When I do: Select sum(sell) from Table, I get the answer: 2,568.78, but
when I add up the numbers on a calculator I get: 2,568.72, off by 6 cents.
Needless to say, this drives accountants nutty. Changing the data type isn't
an option since this a commercial software product we're using (the
developers know about the problem, but choose to let their reports be off by
pennies). We'd like an accurate Sum() amount, and we've tried doing
Round(@.SumSell,2) but that doesn't work.
Any ideas? THanks!I've been messing with this a little to try to duplicate your problem, but
can not..
Can you post a create table, and insert statements, and a select statement
which repros the problem?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23GaANOiXEHA.2844@.TK2MSFTNGP11.phx.gbl...
> I have a table with a field called "Sell" which is:
> Decimal 9, precision=10, scale=2
> When I do: Select sum(sell) from Table, I get the answer: 2,568.78, but
> when I add up the numbers on a calculator I get: 2,568.72, off by 6 cents.
> Needless to say, this drives accountants nutty. Changing the data type
isn't
> an option since this a commercial software product we're using (the
> developers know about the problem, but choose to let their reports be off
by
> pennies). We'd like an accurate Sum() amount, and we've tried doing
> Round(@.SumSell,2) but that doesn't work.
> Any ideas? THanks!
>
Decimal 9, precision=10, scale=2
When I do: Select sum(sell) from Table, I get the answer: 2,568.78, but
when I add up the numbers on a calculator I get: 2,568.72, off by 6 cents.
Needless to say, this drives accountants nutty. Changing the data type isn't
an option since this a commercial software product we're using (the
developers know about the problem, but choose to let their reports be off by
pennies). We'd like an accurate Sum() amount, and we've tried doing
Round(@.SumSell,2) but that doesn't work.
Any ideas? THanks!I've been messing with this a little to try to duplicate your problem, but
can not..
Can you post a create table, and insert statements, and a select statement
which repros the problem?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23GaANOiXEHA.2844@.TK2MSFTNGP11.phx.gbl...
> I have a table with a field called "Sell" which is:
> Decimal 9, precision=10, scale=2
> When I do: Select sum(sell) from Table, I get the answer: 2,568.78, but
> when I add up the numbers on a calculator I get: 2,568.72, off by 6 cents.
> Needless to say, this drives accountants nutty. Changing the data type
isn't
> an option since this a commercial software product we're using (the
> developers know about the problem, but choose to let their reports be off
by
> pennies). We'd like an accurate Sum() amount, and we've tried doing
> Round(@.SumSell,2) but that doesn't work.
> Any ideas? THanks!
>
rounding numerics
Hello,
Executing this statement outputs 10.0000000000. I expect it to be 9.999999999.
declare @.test NUMERIC(24,10)
declare @.test2 NUMERIC(24,10)
set @.test2 = 0.0000000000
set @.test = 9.999999999
select @.test * (1 - @.test2)
Changing the type of @.test2 to VARCHAR(12,10) corrects the problem. I don't understand why SQL Server does this rounding?
Thanx,
Wouter
Hi,
You need to change the script like this to avoid an implicit cast to an integer:
declare @.test NUMERIC(24,10)
declare @.test2 NUMERIC(24,10)
set @.test2 = 0.0000000000
set @.test = 9.999999999
select @.test * cast(1 - @.test2 as NUMERIC(24,10))
Greetz,
Geert
Geert Verhoeven
Consultant @. Ausy Belgium
My Personal Blog
|||At least part of the answer lies with the fact that the "1" in the statement:
select @.test * (1 - @.test2)
is an integer datatype and must be converted for all of the operations to take place. This can be avoided by performing explicit converting to the numeric(24,10) datatype such as:
select @.test * convert (numeric(24,10), (convert (numeric(24, 10),1) - @.test2)) as converted
-- converted
-- -
-- 9.999999999
Also beware that converting to a float might not be exactly what you want either:
|||{ Obviously, I am in agreement with Geert. :-) }select @.test * convert (float,1) - @.test2 as floater
-- floater
-- --
-- 9.9999999989999999
Rounding Modes and .NET Runtime Consistency
We have encountered something of a consistency issue in doing decimal type rounding between SQL Server 2005 and the .NET 2.0 runtime.
SQL Server seems to be using round to zero for Decimal and Money values. However, the .NET runtime, by default, uses round to even (also called 'Bankers Rounding'.) We are therefore stuck between a number of unpaletable options. We can:
Change every instance of rounding in our .NET codebase to use the overloaded rounding to force midpoint rounding.
Add a .NET extension to the database and call it in place of the built-in ROUND function.
Write our own banker's rounding function in T-SQL and call it.
Find a setting in either .NET or SQL Server to change the default rounding mode.
The first three all seem pretty nasty, though the middle two are probably the easiest within our (relatively speaking) C# heavy codebase. I seem to have pretty well eliminated finding a .NET setting (though I may have missed something.)
Is there an appropriate setting in SQL Server to do this? As yet I have not found one.
Andrew Raymond
Mitchell 1
Andrew.Raymond@.mitchell1.com
I don't believe there is any setting available to change the rounding scheme used in SQL Server, at least I can't remember ever having seen one...
/Kenneth
Subscribe to:
Posts (Atom)