Problem in round - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem in round

Author

Topic

abhi143
Starting Member

32 Posts

Posted - 2006-03-10 : 02:55:18

Hi all of u...

Iam having two field which is character data type.
1) 1st Field value = 12.5000
2) 2nd Field value = 0.3333

I want to do multiplication for the two field and it will round the Answer up to 4 decimal place.

i have used the following query.

select str(round(cast(12.5000) as float(8)) * cast(0.3333 as float(8)),4),11,4)

It gives me output, Ans- 4.1662(With Rounding up to 4 decimal)It gives me output, Ans- 4.16624999999(Without Rounding)

But actually value should be come as 4.1663.(Rounding up to 4 decimal)

I cant understand where is the problem......

please use only above mentioned values...because in other filed values its working fine..

please help me...

thanks

abhi

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2006-03-10 : 03:04:38

[code]
SELECT CAST(12.5 * 0.3333 AS DECIMAL(10,4))
[/code]

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt? http://www.insidesql.de/blogs

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-10 : 03:08:12

>>select str(round(cast(12.5000) as float(8)) * cast(0.3333 as float(8)),4),11,4)

Why do you want to convert it into string?

Madhivanan

Failing to plan is Planning to fail

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2006-03-10 : 03:21:01

Oops, if you want it as a string, for whatever reason, what about


SELECT LTRIM(CAST(12.5 * 0.3333 AS DECIMAL(10,4)))

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt? http://www.insidesql.de/blogs

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-10 : 03:42:50

What is the reason for using floating point ? Floating point data is approximate. Unless you need high precision, you can use decimal instead.

From Books Online

quote:
float and real
Approximate number data types for use with floating point numeric data. Floating point data is approximate; not all values in the data type range can be precisely represented.

----------------------------------
'KH'

abhi143
Starting Member

32 Posts

Posted - 2006-03-10 : 03:57:33

thanks for u r help

But please note that, i have to store answer in character.

thats why..its not working..

please help

abhi

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-10 : 04:03:23

>>i have to store answer in character

What is the reason?

Madhivanan

Failing to plan is Planning to fail

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2006-03-10 : 04:12:44

quote:
Originally posted by abhi143

thanks for u r help

But please note that, i have to store answer in character.

thats why..its not working..

please help

abhi

You have seen my answer?

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt? http://www.insidesql.de/blogs

abhi143
Starting Member

32 Posts

Posted - 2006-03-10 : 05:36:39

yes..i have seen your answer..

But 12.5 field value is a char data type in DB.
and we have store result in Char type field.

thanks

abhi

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2006-03-10 : 06:57:32

So, what's the problem?


CREATE TABLE showme (a VARCHAR(7), b VARCHAR(7))
INSERT INTO showme SELECT '12.500', '0.3333'

SELECT LTRIM(CAST(CAST(a AS DECIMAL(8,4)) * CAST(b AS DECIMAL(8,4)) AS DECIMAL(8,4)))
  FROM showme

DROP TABLE showme
                                         
---------------------------------------- 
4.1663

(1 row(s) affected)

Sorry, forget to add the LTRIM() again.

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt? http://www.insidesql.de/blogs

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-10 : 07:58:44

>>But 12.5 field value is a char data type in DB.

If it for storing numeric data, then use Proper datatype.

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-10 : 08:33:52

What is with the disturbingly popular "trend" of not using proper datatypes lately? The last 6 months or so, it seems at least once per day someone posts a problem they are having with their data and it ends up being the same thing over and over -- they did not use the correct datatype.

is there anything more basic than choosing the correct datatype for your data?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-10 : 12:08:36

quote:
Originally posted by jsmith8858

What is with the disturbingly popular "trend" of not using proper datatypes lately? The last 6 months or so, it seems at least once per day someone posts a problem they are having with their data and it ends up being the same thing over and over -- they did not use the correct datatype.

is there anything more basic than choosing the correct datatype for your data?

It's selection bias.

The people that know enough to pick the correct datatype are not posting questions like this.

The people that pick the wrong datatype are likely to have little clue about data conversion, so they end up here when they have a problem.

From our point of view, we can't see all the people in the first category, so it looks like a lot.

I guess storing it as character seems attractive at first, because you don't have to define the true datatype. You don't have to use your brain up front.

CODO ERGO SUM

aiken
Aged Yak Warrior

525 Posts

Posted - 2006-03-13 : 15:32:48

Speaking of someone who was guilty of this in my early days with sql, my rationale was "I'm not sure what kind of data is going to go hear, so I'll just store it as character data and cast it as needed; that way if the numbers get really big or small, I won't have to alter the table."

Hey, it was a long time ago. I was young, and I needed the money.

-b

jhermiz

3564 Posts

Posted - 2006-03-29 : 14:42:17

lol

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2006-03-29 : 15:03:54

You've got a fundamental problem, in that you are using a non-structured type (CHAR) and want a structured result. Worse yet, you want it structured according to very specific rules (number of places, how you want the rounding done).

The correct answer would be to make the columns the correct datatypes. This would fix these problems, and many more that you'll probably find soon. A simple crutch to get you past this particular problem would be:

SELECT Str(Cast('12.5' AS MONEY) * Cast('0.3333' AS MONEY), 10, 4)

-PatP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources