Upadting a numeric field

[ashab02]

Hello

I am wanting to update a numeric field to that it displays the value divided by 100

I have selected the field using the following query


SELECT NUMERIC_VALUE, (NUMERIC_VALUE/100) FROM DETAILS.

Its displays the correct data but I also want to maybe write a where clause which checks if the field is already in the correct format, if this is the case then I would like to exclude them records for the update.

Any help appreciated.

 

[vongrunt]

Suppose there are two values: 2.00 and 200.00.

How would ya determine correct "format" in these cases?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[ashab02]

Hello

I'm safe to assume they will all be in 200.00 format, my other question is that when I perform that query how do I format the columns so its rounded to two decimal place?

Thanks for the help so far

 

[SQLDenis]

>>how do I format the columns so its rounded to two decimal place?
Use ROUND
select ROUND(123.9995, 2)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[druer]

I think the idea of updating the field is a bad plan, unless that will be done at the same time as an application conversion so that all new data goes into the field already divided by 100 at the same time.

The way you asked the question implies that you will have mixed values in the table some already converted and some not, and you probably know the data and where it is coming from better than anybody else, but as Vongrunt pointed out if a value was previously 20,000 and was formatted per your update it would now be 200.00. Or it could have been 2.00 originally and need to become .02.

Formatting of values should be done by the UI on the way out or on the way in, but mixing things up so that the data is still coming in in whole numbers and then converted to percents later seems like a plan that will lead to problems. Any query that reports on the data before you run the conversion job will be wrong. Any report that runs before the conversion job will be wrong.

Consider just running the formatting on the front ends, or change everything over at the same time.

Just a thought,
Dalton