Float to decimal - 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

Transact-SQL (2000)

Float to decimal

Author

Topic

kristine
Starting Member

25 Posts

Posted - 2006-09-13 : 02:53:00

hi..is it possible to convert a float value to numeric value? like i would have wanted to have it into decimal so that i can truncate it's trailing zeroes..i did try though but i got this error:

"Arithmetic overflow error converting float to data type numeric.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-13 : 02:56:51

what is the float value ? What is the precision & scale of the numeric ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-13 : 02:58:26

Please post the code which gives error...you may be specifying less precision for Numeric data type than the float value.

Harsh Athalye
India.
"Nothing is Impossible"

kristine
Starting Member

25 Posts

Posted - 2006-09-13 : 02:58:45

here's the code i created..the datatype of the numericgrade column is float

<code>SELECT convert(decimal(4,2),(case when NumericGrade IS NULL THEN '' ELSE NumericGrade END))
FROM FinalClassGrade</code>

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-13 : 03:03:16

quote:
Originally posted by kristine

here's the code i created..the datatype of the numericgrade column is float

<code>SELECT convert(decimal(4,2),(case when NumericGrade IS NULL THEN '' ELSE NumericGrade END))
FROM FinalClassGrade</code>

What is the precision for the NumericGrade column (e.g. Float(12))? What is the current biggest value in the NumericGrade column?

Harsh Athalye
India.
"Nothing is Impossible"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-13 : 03:07:08

quote:
Originally posted by kristine

here's the code i created..the datatype of the numericgrade column is float

<code>SELECT convert(decimal(4,2),(case when NumericGrade IS NULL THEN '' ELSE NumericGrade END))
FROM FinalClassGrade</code>

You can't convert an empty string '' to decimal(4,2)

When NumericGrade is NULL what is the resulting decimal value that you want ? ZERO or NULL ?


if you want ZERO 
select convert(decimal(4,2), isnull(NumericGrade, 0)) or
select isnull(convert(decimal(4,2), NumericGrade), 0)

or if you want NULL
select convert(decimal(4,2), NumericGrade)

kristine
Starting Member

25 Posts

Posted - 2006-09-13 : 03:27:02

well..ok..here's the complete code i really wanna execute:

<code>SELECT (convert(decimal(4,2),(case when NumericGrade IS NULL THEN '' ELSE NumericGrade END))) + ' ' +
(case when AlphaGrade IS NULL THEN '' ELSE AlphaGrade END)
FROM FinalClassGrade<\code>

so with that, you'd know why i put the case thing..because the thing is, i'll have to choose whichever of the two has values..that is, either numeric or alpha..

also, when i convert the first selected item that i have, that is the numericgrade, i also got an error that i can't convert the datatype varchar to numeric..i don't intend to though, it's just that the value is supposed to be concatenated so that i can have a result for either of the two values..how can i resolve it?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-13 : 03:34:06

will this do for you ?


select convert(varchar(10), isnull(convert(decimal(4,2), NumericGrade), '')) +
       isnull(AlphaGrade '')
from   FinalClassGrade

Note :

case when AlphaGrade IS NULL THEN '' ELSE AlphaGrade END

can be simplify to

isnull(AlphaGrade, '')

EDIT : typo & spelling err

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-13 : 03:35:00

quote:
Originally posted by kristine

well..ok..here's the complete code i really wanna execute:

<code>SELECT (convert(decimal(4,2),(case when NumericGrade IS NULL THEN '' ELSE NumericGrade END))) + ' ' +
(case when AlphaGrade IS NULL THEN '' ELSE AlphaGrade END)
FROM FinalClassGrade<\code>

so with that, you'd know why i put the case thing..because the thing is, i'll have to choose whichever of the two has values..that is, either numeric or alpha..

also, when i convert the first selected item that i have, that is the numericgrade, i also got an error that i can't convert the datatype varchar to numeric..i don't intend to though, it's just that the value is supposed to be concatenated so that i can have a result for either of the two values..how can i resolve it?

Have you read KHTAN's post? YOU CAN'T CONVERT EMPTY STRING TO DECIMAL(4,2)...That's why you are getting error "cannot convert varchar to numeric"

Also you can't concatenate a Decimal value to string, you have to first explicitly convert decimal to varchar and then do concatenation like this...

SELECT 
	Convert(Varchar(10), (convert(decimal(4,2),
		(case 
			when NumericGrade IS NULL THEN 0 
			ELSE NumericGrade 
		END)
		)
	)) + ' ' + 
	(case 
		when AlphaGrade IS NULL THEN '' 
		ELSE AlphaGrade 
	END) 
FROM FinalClassGrade

Harsh Athalye
India.
"Nothing is Impossible"

kristine
Starting Member

25 Posts

Posted - 2006-09-13 : 03:47:59

quote:

Originally posted by khtan

will this do for you ?


select convert(varchar(10), isnull(convert(decimal(4,2), NumericGrade), '')) +
       isnull(AlphaGrade '')
from   FinalClassGrae

Note :

case when AlphaGrade IS NULL THEN '' ELSE AlphaGrade END

can be simplyfy to

isnull(AlphaGrade, '')

nope..i still got the same error.."error converting data type varchar to numeric"..i also modified mine into something like this..

SELECT case when NumericGrade IS NULL THEN (isnull(AlphaGrade, '')) ELSE convert(decimal(6,2), NumericGrade) end  
	 FROM FinalClassGrade

kristine
Starting Member

25 Posts

Posted - 2006-09-13 : 03:57:36

quote:

SELECT 
	Convert(Varchar(10), (convert(decimal(4,2),
		(case 
			when NumericGrade IS NULL THEN 0 
			ELSE NumericGrade 
		END)
		)
	)) + ' ' + 
	(case 
		when AlphaGrade IS NULL THEN '' 
		ELSE AlphaGrade 
	END) 
FROM FinalClassGrade

i think this one works..thanx to both of you:)..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-13 : 04:00:53

you are still converting '' to decimal

Note the below will return error "Error converting data type varchar to numeric."
select convert(decimal(6,2), '')

When you have a column of different data type, SQL Server will try to convert from one to another. In this case, converting varchar to decimal. When NumericGrade is NULL value, you are converting to varchar (empty string). When it is not NULL, it is converted from float to decimal

Take a look at the following
NumericGrade column

original         ->      converted
123.345                  123.34
NULL                     ''

So after converted, you have a mix of varchar & decimal datatype. And SQL Server will attempt to convert the varchar to decimal. And as '' can't be converted to decimal you will get the error message you posted.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-13 : 04:03:13

Strangely you can't convert '' to decimal but you can convert to float


select convert(decimal(6,2), '')
-- Error converting data type varchar to numeric.

select convert(float, '')
-- Result 0.0

Anybody can explain this ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-13 : 04:04:56

quote:

Originally posted by kristine

quote:

SELECT 
	Convert(Varchar(10), (convert(decimal(4,2),
		(case 
			when NumericGrade IS NULL THEN 0 
			ELSE NumericGrade 
		END)
		)
	)) + ' ' + 
	(case 
		when AlphaGrade IS NULL THEN '' 
		ELSE AlphaGrade 
	END) 
FROM FinalClassGrade

i think this one works..thanx to both of you:)..

Here is another shorter version:

SELECT 
	Convert(Varchar(10), convert(decimal(4,2), IsNull(NumericGrade,0))) + ' ' + IsNull(AlphaGrade, '')
FROM FinalClassGrade

Harsh Athalye
India.
"Nothing is Impossible"

Kristen
Test

22859 Posts

Posted - 2006-09-13 : 04:07:47

"... so that i can truncate it's trailing zeroes"

Boring answer, but if this is for display purposes (to users) then it should be done int he presentation layer, not in SQL Server.

Kristen

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-13 : 04:16:09

quote:
Originally posted by khtan

Strangely you can't convert '' to decimal but you can convert to float

select convert(decimal(6,2), '')
-- Error converting data type varchar to numeric.

select convert(float, '')
-- Result 0.0

Anybody can explain this ?

KH

I think it has to do with the type of numeric data they hold.
While Numeric and Decimal holds fixed precision numbers, Float and Real stores floating precision numbers.

So converting '' to Float or Real works but not for Decimal or Numeric !

Harsh Athalye
India.
"Nothing is Impossible"

Kristen
Test

22859 Posts

Posted - 2006-09-13 : 04:23:45

I'm not sure I've ever been a fan of the silent casting from '' to 0 (or "1900-01-01 00:00:00.000" in the case of a datetime).

So perhaps DECIMAL has got it right and int, money, datetime, float ... are the ones that are wrong?!

Kristen

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-13 : 04:26:46

quote:
Originally posted by Kristen

I'm not sure I've ever been a fan of the silent casting from '' to 0 (or "1900-01-01 00:00:00.000" in the case of a datetime).

So perhaps DECIMAL has got it right and int, money, datetime, float ... are the ones that are wrong?!

Kristen

or maybe this is Microsoft's so called User-Friendliness (or user-headache) ?

Harsh Athalye
India.
"Nothing is Impossible"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-13 : 04:36:06

quote:
Originally posted by Kristen

I'm not sure I've ever been a fan of the silent casting from '' to 0 (or "1900-01-01 00:00:00.000" in the case of a datetime).

So perhaps DECIMAL has got it right and int, money, datetime, float ... are the ones that are wrong?!

Kristen

This sounds more logical

. Hope this is not one of the surprises you get when moving from 2000 to 2005.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources