Tracker
HOME    TRACKER    MS SQL Server
 SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.

Round To Nearest WHOLE Number

T-SQL: How to round to the nearest WHOLE number ? so from -- to ------------- 170 --170 96.58 --97 thanks

Related Messages:
Retrieving Nearest Number From Database

OK, this is the scenario. I have a database with many columns ( each a mean value and a standard deviation, and with it a set of coordinates that i want to retrieve ).

Then i have a value that i want to query with the database, by comparing it with the mean and its standard deviation, and it should return a few sets (lets say 2) of coordinates whereby the the value of the mean is closet to the one in the database, in order of nearest value. How should i do it, since i am not using the exact value of the mean in the database?

I know its a bit confusing the way i wrote, but anyone understand wat i am trying to say and can help, i am very grateful. I had googled around for answers but cannot find. Thanks.

Round A Number
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

Why Does SQL Server Round Number Of Type Double?
Dear Programmers,

I want to store a number of .NET type double (16,95) into the database
column of type numeric, but it is stored as integer (17) and when I call the
value in my application, it is displayed as 17. How can I solve this

Burak

How To Disable Autmatic Round Of Of A Decimal Number In Sql Server Using Asp.net?
Hi all,
I am sorry if i am posting this error in an inappropriate froum.
Well in my asp.net intranet web application i want to enter a number to the database(sql sever 2005) that has a column(schoolkm) whose type is decimal(9, 2). Now if i want to enter the value 1.5 in the text box and enter that value to the database through interface then that value automatically rounds to 2. But when i get into the table and enter that value by hand then that value enters perfectly i.e. without rounding of. I want to know the reason and how can i cure this problem.

Nearest Distance
Hi
How do I get a nearest distance of a point? For example, I have two tables A and B and I want to find the nearest distance between the records of the two tables. In addition, one of the tables should also give me the distance. The data I have geo spatial data. Can this be done in SQL
Help will be appreciated

List By Nearest Date?
Hi, I'm making a birthdays database where I want to list everyone in it ordered by the firstcoming birthdays according to the current date.

I have all the birthday records stored in a table called bursdager and the person name is stored in the navn column and the persons birthday date is stored in the dato column.

I'm having some problems, currently I have this statement:

Code:

DECLARE @tbl TABLE (navn VARCHAR(60), dato DATETIME)

INSERT INTO @tbl
SELECT navn, dato FROM bursdager
WHERE DATEPART(month, dato) >= DATEPART(month, getdate())
ORDER BY DATEPART(month, dato), DATEPART(day, dato)

INSERT INTO @tbl -- those are the one who allready have had birthday this year
SELECT navn, dato FROM bursdager
WHERE DATEPART(month, dato) < DATEPART(month, getdate())
ORDER BY DATEPART(month, dato), DATEPART(day, dato)

SELECT * FROM @tbl

It works *allmost* as it should-- except, it still lists the last persons who had birthday first, even the days after their birthday if the month is still the same.

Code:

AND DATEPART(day, dato) >= DATEPART(day, getdate())

in the WHERE clause of the SELECT statement but that won't be correct either because it then just lists everyone based on whether the day number the person was born is higher or less than the day number of the current date.

Anyone have any suggestions? Is there an easier way to do it?

Dag

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

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.

Optimal Search For The Nearest Date

Joining Records By Nearest Datetime
Hi,

I have such a scenario:
- two tables with record containing car vehicle number, datetime of message and other data like weight ect.
- first table contains only two messages for one car per one day
- second has many messages for one car for one day

I would like to get a list of messages from first table but joined with the nearest (previous) record for the same car from second table.

Thanks,
Przemo

Query - Nearest Birthdays To Mine
I have a date (my birthday). I would like to find the closest birthdays to mine, both before and after my birthday. I would like to list the people in my database who are the closest age to me, but in that order. So sorting my table by age and taking a row below and above my birthday is not going to work. This is because the three people below me may all have their birthday the next day, while those above me may have theirs years before mine.

Birthdays sorted by date:

05/10/1979   jim
12/01/1980   bob
10/04/1983   mike
10/05/1983   larry
11/21/1983   dan
12/07/1984   josh
05/07/1999   dylan

The order I wish to achieve is:

10/05/1983   larry
11/21/1983   dan
12/07/1984   josh
12/01/1980   bob
05/10/1979   jim
05/07/1999   dylan

Mike

T-SQL ROUND(decimal, Int) Vs C# Round(Decimal, Int32)
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

Changing Seconds To Hours And Minutes And Rounding To The Nearest 15 Minute.
I found in another forum that if I take the seconds and divide them by 15 then round up and multiply them by 4 I can get this done, but I can't figure out how to work it into my select statement.  Anyhelp would be greatly appreciated.  dbo.SLPTRANS.TimeSpent is the field I am trying to convert.

SELECT     dbo.SLPTRANS.ClientID, SUM(dbo.SLPTRANS.TransValue) AS Expr1, dbo.SLPTRANS.TimeSpent AS Expr2
FROM         dbo.SLPTRANS INNER JOIN
dbo.INVOICE ON dbo.SLPTRANS.InvoiceID = dbo.INVOICE.RecordID
GROUP BY dbo.SLPTRANS.ClientID
HAVING      (dbo.SLPTRANS.ClientID = 405)

Round Up And Round Down In Sql
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

Round Up
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?

How ROUND?
Hi,i need to round:3° decimal between 1 and 5 LOW3° decimal between 6 and 9 UPThanks

Round ?
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?

Round Bug
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.

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

How Do I Round The Value ?
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)

Round Off
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

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

Round To Near Value
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

Round The Value
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

Round Up
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?

Round

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

Round Problem
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.

Cast And Round
I am using a statement in which a where clause has a statement like this

Where ..
and cast(round(colname,1) As Numeric(9,1)) =>9.5

When i execute this query i get this error

Server: Msg 8114, Level 16, State 5, Procedure sp_rpt_get_count_hb_great_9andhalf, Line 22
Error converting data type varchar to float.

Any help is appreciate

Problems With Round()
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?

Get Round Of Getdate()
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

Round Function
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

Round() Problem
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.

Can't Seem To Round An Average.
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.

Rounding - Always Round Up
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?

How To Round With Negative Numbers?
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

ROUND Unexpected Result
/*********** 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 ?

Strange ROUND Function!!!
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:

CAST And ROUND (MS SQL 2005)
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

Round Function (Zero Elimination)
Hi

I use round function

select round(1234.4545,2)
GO

Result
1234.4500

I want last two zero to be eliminate.

Thanks

SQL Query - 'ROUND' COMMAND
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.
Anna - Verona (Italy)

ROUND Function In Ms-sql Server
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

Executing N Procedures In 1 Round Trip
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?

Round And Show To 2 Decimal Places?
Hi All,

I have a small question to ask. How to round a numeric field upto 2 decimal places, and also show it with 2 decimal places only

For example the following would return 255.88000000000
select round(255.87908765444,2)

How to get 255.88 only?

Pleas help.

Thanks,

Zee

Round Up Float In SQL Server 2005

Greetings:

Is there a way of rounding up floating point numbers without CASTing as shown in a number of the replies ?  For example, a table value is 103.365 and I'd like to see values of 103.365 and above (103.366,103.367, 103.368, 103.369) rounded to 103.37.  Is is possible to do this with a function or setting in SQL Server 2005 or is it necessary to write T-SQL to do the rounding ?

Thanks.

alan