473,372 Members | 1,078 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

Round funtion on entire columns in MSSQL?

Hi,

I'd like to round all amounts in a certain column to 2 decimals.
I tried the following query, but eventhough the syntax is correct, it
doesn't give any result:

update gbkmut
set bdr_hfl = round(bdr_hfl,2)

can anyone help me?

cheers,

steve
Jul 20 '05 #1
3 7131
On 5 May 2004 02:40:15 -0700, steve wrote:
Hi,

I'd like to round all amounts in a certain column to 2 decimals.
I tried the following query, but eventhough the syntax is correct, it
doesn't give any result:

update gbkmut
set bdr_hfl = round(bdr_hfl,2)

can anyone help me?

cheers,

steve


Hi Steve,

What do you mean with "doesn't give any result"?

If you mean that no rows were returned by the update statement, then
this is expected behaviour. An UPDATE-statement will update the data,
nothing more nothing less. Use SELECT if you want to see anything.

If you mean that after executing the above UPDATE, you still have data
with more than two non-zero digits after the decimal point, I'd ask
you to post more details (table definition in the form of CREATE TABLE
statements, sample data in the form of INSERT statements, expected
output and the output you got) so that others can try if they can
reproduce this apparantly erroneous behaviour.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #2
st******************@exact.be (steve) wrote in message news:<79**************************@posting.google. com>...
Hi,

I'd like to round all amounts in a certain column to 2 decimals.
I tried the following query, but eventhough the syntax is correct, it
doesn't give any result:

update gbkmut
set bdr_hfl = round(bdr_hfl,2)

can anyone help me?

cheers,

steve


At first glance, your UPDATE statement seems to be OK. Can you give
some more information? In particular, what is the data type of the
bdr_hfl column, and can you give some sample values, as well as your
expected result? And what does "doesn't give any result" mean?

Simon
Jul 20 '05 #3
steve (st******************@exact.be) writes:
I'd like to round all amounts in a certain column to 2 decimals.
I tried the following query, but eventhough the syntax is correct, it
doesn't give any result:

update gbkmut
set bdr_hfl = round(bdr_hfl,2)


There is very little information, but I would guess that your column is
of datatype float. Float is an approxamite datatype, which means that
far from all values can be stored exactly in a float. For instance,
try this:

select convert(float, 1.89)

In Query Analyzer this displays as 1.8899999999999999.

In many situations, it is possible to cope with these extra decimals at
the end; you only need some care. If you need exact numbers, you must
use the decimal type instad. The drawback is that with decimal, you
must decide from the beginning which range you handle.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: steve | last post by:
Hi, I'd like to round all amounts in a certain column to 2 decimals. I tried the following query, but eventhough the syntax is correct, it doesn't give any result: update gbkmut set bdr_hfl...
1
by: msnews.microsoft.com | last post by:
Hi Every Body, I have tow columns in the database, the third columns the division of both columns. The first two columns are both of type decimal, when the third columns get value its type is...
2
by: Irfan | last post by:
hi, When i access data from Access and place it in datagridview, the values are not rounded off. For example: In access i can see the value as 5.7 but when it is shown on the grid it is...
7
by: bravesplace | last post by:
Hello, I am using the folling funtion to round a number to a single digit on my form: function round1(num) { return Math.round(num*1)/1 }
5
by: Lee Xuzhang | last post by:
/* from SICP -- Exercise 4.21: ((lambda (n) ((lambda (fact) (fact fact n)) (lambda (ft k) (if (= k 1) 1 (* k (ft ft (- k 1))))))) 10) */
0
by: thaisummitneel | last post by:
sir I have created a database in ms-access.Connected database using ADODB I have written a query to round integer to nearest value such as con.execute"update tablename set...
2
by: larmores | last post by:
I have a medical database being exported to fixed-width text file and then need to ftp. I have a data source and can extract 6 of the seven tables via a Transform Data Task connected to a Text File...
0
by: tangoal | last post by:
I have a table1 as: id(int); percent(double,2); a(double,2); b(double,2); c(double,2);.....z(double,2) 1; 0.12; 1.33; 1.26; 1.89;........ 2; 0.11; 1.55; 1.22; 1.56;........ : : and many...
6
by: dkirkdrei | last post by:
I am looking for a way to round a number (which will be displayed as a dollar amount) to the nearest nickel. Using the php round function only allows you to control the number of decimal places in...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.