Round Bug

Mar 25, 1999

Has anyone been experiencing problems with rounding to 2 decimal places in SQL 7? I have a bunch of queries that generate web reports. Under 6.5 everything was fine. Now under 7.0 any number that needs to be rounded to 2 decimals is actually giving me several decimal places.

Here is a simplified version of what I am doing:
DECLARE @x real
DECLARE @y real
SELECT @x = 223.1
SELECT @y = 59.7

SELECT ROUND((@x/@y),2)

Result should be 3.74. But instead I am getting 3.7400000000000002

If anybody has heard if Microsoft has declared this as a known bug please let me know.

Thanks for your time.

View 1 Replies

T-SQL ROUND(decimal, Int) Vs C# Round(Decimal, Int32)

Jan 30, 2006

Anybody noticed that SQL Server rounds up if the value is half waybetween two rounded values, but C#'s Decimal.Round(Decimal,Int32)rounds to nearest even number?[color=blue]>From MSDN: "When d is exactly halfway between two rounded values, the[/color]result is the rounded value that has an even digit in the far rightdecimal position. For example, when rounded to two decimals, the value2.345 becomes 2.34 and the value 2.355 becomes 2.36. This process isknown as rounding toward even, or rounding to nearest."I perform the same calculation sometimes on the web server in C# andsometimes at the database in T-SQL, but want to get the same resultfrom both calculations. Could anybody offer any strategies for dealingwith this?Thanks ~ Matt

View 3 Replies View Related

Round Up

Sep 14, 2006

X is a float and I need to perform x/10 and round the result up to the integer. So if result is 0.4 -> 1, if 1.1 -> 2. How can I do this with SQL?

View 1 Replies View Related

Round Up And Round Down In Sql

Sep 9, 2007

I want to do a simple thing but it seems to be behaving not as i am expectingI want to round number either up or down....e.g: 4.3 should round to 4 4.7 should round to 5when i use the round function like this: 83/17=4.88round( 83/17 , 0 ) it gives the answer as 4....when i was expecting it to be 5.... i know there is a ceiling function...but depending on the value of the division sometimes i want it to round up and sometimes round down. how can i do this? hope this makes sense. thanks

View 3 Replies View Related

Round ?

Aug 22, 2002

I have the statement below which I use in an update

select (round(sum(tottime/60),2)) as ttime from vw_cms_suptime
where vw_cms_suptime.[tracking number] = 970
tracking.[tracking number]

Even though I have the round the statement returns a value
of 5.0000000000000003E-2

Is there something wrong with the round?

View 1 Replies View Related

Round The Value

Apr 17, 2008

Hi,

I need to display the value of a certain varible rounded to two digits after decimal.

For example :

value of a is 1346.8500

I need to get the value of a as 1346.85

Please help me

View 4 Replies View Related

Round To Near Value

Jan 4, 2007

hi,i have one criteria

if if i have time as 1:15 min means it has to show 1:15,if i have 1:20 min it has too be rounded and it has to show 1:30 min.
can any one give me query for this
thanks in anvance

View 3 Replies View Related

Round Off

May 14, 2007

How to round off the value .579 into .6 using round function.i tried doing it but in vain.

View 3 Replies View Related

Round Off

Jul 30, 2007

how do i round off value 0.23 to 0.2.
i am using this inside a scalar function and the return type is numeric(6,2).
so how do i get 0.2 and not 0.23

View 4 Replies View Related

How Do I Round The Value ?

Aug 10, 2007

Hi

I have datacloum called 'price' (float) and using the below code in my stored procedure

cast(price * 1.175 as decimal(19, 2)) as [item-price]

I am getting the price as (ex1: 23.58, ex2: 114.25, ....etc)

So How do I round the value(price) to (ex1: 23.99, ex2: 114.99)

Advance thanks

View 3 Replies View Related

Round Off

Aug 23, 2007

i want to round off 0.23 to 0.25 ,i am trying it with round function but can't get it.

View 3 Replies View Related

How ROUND?

Jul 20, 2005

Hi,i need to round:3� decimal between 1 and 5 LOW3� decimal between 6 and 9 UPThanks

View 1 Replies View Related

Round

Oct 10, 2007

There is a filed in a table of type decimal(12, 8)

when running a select query on this table for this field i.e.
select field1 from table1

the data is shown like:
102.12500000
104.12500000

And therefore the report shows the same figure.

How do I get this field to show up in the report as 4 decimal places. i.e.
102.1250
104.1250

Tried the properties of the cell to format the text into a number but there is only 2 decimal places there.
Even tried the expression by using Rnd(field1). This does not seem to do what I am after.

Thanks

View 1 Replies View Related

Round A Number

Jun 26, 2000

How do I write sql syntax to round the last digit. For example, 12.152 should be 12.15. My current sql syntax does not round the last digit as follows:

Select tblARInvoiceDetail.UnitPrice * tblARInvoiceDetail.Quantity AS ChargeBeforeDiscount
FROM tblARInvoices INNER JOIN
tblARInvoiceDetail ON
tblARInvoices.ARInvoiceID = tblARInvoiceDetail.ARInvoiceID

The tblARInvoiceDetail.UnitPrice column is numeric;length 9;precision 18
The Quantity column is integer;length 4; precision 10;scale 0

Any Suggestions?
Thanks,
Denise

View 1 Replies View Related

Round Problem

May 5, 2004

Hi,

I am using sql statement to save data in SQL SERVER but even i did not apply any round function it is automatically rounding up. e.g. 3.56 when i see it in database it is 4 how can i avoid this rounding? I am using MS Access as front end.

Any help will be highly appreciated.

View 11 Replies View Related

Round Function

Jun 18, 2008

I have a money field, which I want to Round price in it to nearest .95 cent . The problem I have is, I don't know how to give 0.95 to Round function.

Thanks
Mazdak

View 1 Replies View Related

How To Find Max Round

Feb 12, 2014

I've a table having the following fields :

state|dist|company|round
1 | 1 | 2 | 2
1 | 1 | 3 | 3
2 | 1 | 3 | 2
2 | 1 | 1 | 4
2 | 2 | 12 | 1
2 | 2 | 9 | 2
2 | 2 | 4 | 3

I want to extract the max round in the state,district with the adjacent company.

View 3 Replies View Related

Round Off Sum Values

Mar 19, 2014

I have already summed the values and this below query sum the values and returns zero if there are no values. How can I roundoff the values for these columns.

isnull(sum([Old_Values]),0) [Old Values],
[New_Value] = isnull(sum([Yearly_Value]-[Previous_Year_Value]),0)

Even this query I use for calculation and I need to round off the values

[Product_Price] = [Product_Profit]/4,
[Product_Profit] = [ActualValue] * 0.75,
[GrossValue]

View 1 Replies View Related

Problems With Round()

Feb 19, 2008

I understand that when you use ROUND(8.5,1), I would get 9 but no matter how hard I try, I get 8.

I had this code that suppose to get the round of a quotient. I wanted to update a number of records by converting them via multiplication and/or division.

Here:

UPDATE Length
SET inchTOcm = inchTOcm*2.5, metricTOton = ROUND(metricTOton/1.1,1);

supposing the original value of inchTOcm are in inch and in metricTOton are in metric ton.

There is a value in the metricTOton that when I divide it with 1.1, it's 345.81818. However, when I round it, it displays 345 instead of 346.

Is there wrong? How can I do this without using the CASE statement?

View 3 Replies View Related

Get Round Of Getdate()

Mar 1, 2008

Hi,
i write a query

select getdate()

suppose output come '2008-03-01 14:08:52.187'

i want to get like this output

'2008-03-01 14:09:00'

means want to neglate second and milisecond part and want round of getdate()

Ranjeet Kumar Singh

View 1 Replies View Related

Round() Problem

May 20, 2008

Hi,

When I use the following query
select round(7.35,1)
the output is 7.4.

But when I use this query

declare @tot float

set @tot = 7.35

select round(@tot,1)

the output is 7.3

How do I make the output consistent here desirable is 7.4. Please advise.

View 4 Replies View Related

Can't Seem To Round An Average.

Feb 8, 2007

I am using an expression to create an average. The result is an odd number like 33.666666666666667.

I want to round this to 34 (I would even accept 33, or 33.67), just not so manny digits to the right.

View 1 Replies View Related

Rounding - Always Round Up

Dec 5, 2007

Hi All,

Is there a way in T-SQL using the round command to always round up regadless of the value.

For example:ROUND(normal_hours * pay_rate * 52, - 3) AS Expr3

normal_hours = 36
pay rate = 23.64
weeks in year = 52

(36 * 23.64) * 52 = 44,254.08

It rounds to 44,000. I want 45,000. Is this possible. Am I overlooking the obvious?

Thanks in Advance and Happy Holidays!!!!!!!!Adam

View 9 Replies View Related

ROUND Unexpected Result

Feb 8, 2006

/*********** Script 1 **************/
declare @nr_1 as decimal (10,2)
declare @nr_2 as decimal (10,2)
set @nr_1=5
set @nr_2=3
select round(@nr_1/@nr_2,0)

RESULT = 2

/*********** Script 2 **************/

select round(5/3,0)

RESULT = 1

What it is the explication for these difference ?

View 1 Replies View Related

Strange ROUND Function!!!

Feb 16, 2007

Set @AVG = ROUND ((V1*5+V2*5)/10,0)

What is the difference between these codes?

Set @AVG = ROUND ((V1*5+V2*5)/10.0,0)

For values V1=80, V2=85;
First code gives 82! Second gives 83!:confused: :confused: :confused:

View 6 Replies View Related

CAST And ROUND (MS SQL 2005)

Mar 27, 2008

for MS SQL 2005 I am having

SELECT name.Name
, Percentage = (count(*) / ((SELECT tot = COUNT(*) * 1.0 ) FROM dbo.Name)) * 100)
FROM dbo.Name
GROUP BY Name

how can i CAST and ROUND (count(*) / ((SELECT tot = COUNT(*) * 1.0 ) FROM dbo.Name)) * 100) ?

then ROUND (the result, 2) ?

CAST(count(*) / ((SELECT tot = COUNT(*) * 1.0 ) FROM dbo.Name)) * 100) AS DOUBLE does not work

thank you for helping

View 4 Replies View Related

T-SQL (SS2K8) :: Round Value To The Nearest

Sep 16, 2014

I have a column called as NDM$ What I want do it round it the nearest value example I am giving below

34.100->34%

39.8->40

35.4->35 some thing like that.

View 6 Replies View Related

ROUND Function In Ms-sql Server

Jun 13, 2006

Hi all

if i run
SELECT ROUND(700/1224) -- from sql server queyr analysier iam getting value as "0"

but same query i run in Oracle

SELECT ROUND(700/1224) FROM DUAL; iam getting value ".571895425"

what is reason can some body explan me

View 4 Replies View Related

SQL Query - 'ROUND' COMMAND

May 26, 2007

Has any of you ever had problems with the ROUND command?
I have this operation:
ROUND((FIELD1 / 360 * FIELD5),3) AS FIELD7
(I want the result to be rounded at the third decimal).
SQL makes mistakes in rounding. It seems to me that
the problem relies in the quantity of decimals taken
into account in any single step of the operation.
Thank you in advance.
Anna - Verona (Italy)

View 5 Replies View Related

Round Function (Zero Elimination)

Jul 19, 2007

Hi

I use round function

select round(1234.4545,2)
GO

Result
1234.4500

I want last two zero to be eliminate.
Please advise how?

Thanks
Jawad

View 3 Replies View Related

How To Round With Negative Numbers?

Jul 23, 2005

I am using a select statement to obtain a result set back with aggregateddata. The problem is that I am seeing column data with 11 to 13 digitsafter the decimal point. I tried using the STR function, but then the OrderBy clause does not sort properly because there are negative numbers in theaggregated data... I tried using Round, but that does no good either - itstill ends up displaying too many digits after the decimal point. Right nowI'm just using Query Analyzer to display the data, so I can live with it fornow. But, in the future, my app will be getting a result set back and Iwould prefer not to have to go through each row and do a round on it fromthe program. Does anyone know how to solve this problem?Thanks for any help,Bob

View 4 Replies View Related

Round To Nearest WHOLE Number

Nov 16, 2006

T-SQL:How to round to the nearest WHOLE number ?sofrom -- to-------------170 --17096.58 --97thanks

View 1 Replies View Related

Executing N Procedures In 1 Round Trip

Apr 30, 2006

w/ SqlServer, is there anyway to pack a number of calls to the same stored procedure into a single round-trip to the DB short of dynamically writing a T-SQL block? For example, if I'm calling a procedure "Update Contact" which takes 2 params @Campaign, @Contact 20 times how would I pass in the values for those 20 diffrent versions?

View 6 Replies View Related