Having conversion issues - String to Float

I am running into some issues with conversion and would appreciate someone who can help on this.

I need to roll up a column which contains numeric (float) data but it is stored in a varchar field.

I was trying to do the following (please note that Activity field is varchar(50) in MyTable):

SELECT CONVERT(float, (NULLIF(Activity,0))) from MyTable

and i get the following error.

Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the varchar value '-39862.8' to data type int.

I even tried with case statement (as below) but still got same issue.

SELECT CASE ISNUMERIC(NULLIF(Activity,0))

WHEN 1 THEN

CONVERT(float, (NULLIF(Activity,0)))

ELSE 0

END

from MyTable

Thanks,

Ashish

Try using 0.0 rather than 0 in the NULLIF functions. Also, do you mean to use ISNULL, or NULLIF? NULLIF returns a NULL, if the expressions are equal, while ISNULL returns the second argument if the first is NULL.
|||Yes. I want to use ISNULL. I changed it but I still get the error. Even with '0.0'|||What happens when you run the following:

Code Snippet

select *

from MyTable

where isnumeric(activity) = 0

|||SELECT CONVERT(float, (NULLIF(Activity,0))) from MyTable where isnumeric(Activity)=1|||

Are you sure that you want to use NULLIF()? (I would think that ISNULL() would be a better option.)

Also note that the isnumeric() test will PASS because '-39862.8' will always test to be a number.

This works as you want.

Code Snippet

DECLARE @.Activity varchar(50)

SET @.Activity = '-39862.8'

SELECT cast( isnull( @.Activity, 0 ) AS float )

--
-39862.800000000003

|||Good point about isnull(), Arnie.

Handling a double or float value for inserting into DataTime field.

Hi,

I was trying to enter the non-normalised exponential format of double or float value into the DataTime field in my data base. It is allowing to store any kind of data passed to this field. If the same non-normalised exponential value for eg: 4.235E-329 is passed to float or double field we are getting a TDS error but when same thing is used to store in DateTime field it is simply inserting the value.

Now my concern is that SQL Server 2005 should give me such kind of exception when I am trying to insert in-valid double or float value to DateTime field. Is this a bug in SQL Server 2005? Please kindly help me how to implement this one.

Thanks,

I get the same results on SQL 2000 and SQL 2005 when I run the script below.

I get
Server: Msg 168, Level 16, State 1, Line 2
The floating point value '4.235E-329' is out of the range of computer representation (8 bytes).

and the value 1900-01-01 00:00:00.000 is inserted into the datetime field and the value 0.0 gets inserted into the float field.

Can you please clarify your question or provide a repro?

Thanks!
use tempdb
go

create table t1(d datetime)
go

create table t2(f float)
go

insert into t1 (d) values (4.235E-329)
go

insert into t2 (f) values (4.235E-329)
go

select * from t1
go

select * from t2
go

drop table t1
go

drop table t2
go

|||

Unfortunately, SQL Server is a bit inconsistent in its compliance with the IEEE floating point standards. The value 4.235E-329 is less than 2^(-1023), and it can only be represented as a reduced-precision "denormalized" value. In some situations, these values will be understood and used correctly, and in others they will generate errors. The best advice I can offer is to be cautious with them, unfortunately. In SQL Server 2000, for example (and I expect in 2005 as well), the first code snippet here will succeed, but the second will fail:

declare @.f float
set @.f = 1e-307
set @.f = @.f/100000000000000
select @.f
go

declare @.f float
set @.f = 1e-322
select @.f
go

Steve Kass
Drew University

|||Problem Repro steps:

Create table tblTest1(id int, fld1 float, dtfld datatime)
Go

insert into tblTest1 values(10, 4.235E-329, '10/29/2005')
Go

This insert will give an error
Server: Msg 168, Level 16, State 1, Line 2
The floating point value '4.235E-329' is out of the range of computer representation (8 bytes).

If we selected the data then we will see there will be no record inserted.

now change the insert statment as

insert into tblTest1 values(10, 4.23, 4.235E-329)
Go

Then this will say that 1 record inserted and when we select the table to display the rows we will see that the record inserted. My Question is that if we insert a wrong non-normalised (Exponential) double or float value into float or double column it is raising an error and the trasaction is rolled back, but if we have given the same value to a datatime field, it is not raising any error and the record is inserted. We want to know is that a Bug in SQL Server 2000 / 2005.

Our main concern is that we want to get a such a kind of exception when we try to insert a wrong non-normalised (Exponential) value into datatime field and the transaction should be rolled back. Is there any work around to get rid of this issue, please mention me

Thanks|||When I ran your scripts on SQL 2005, both insert statements would throw a warning (msg 168), and then the insert would continue and insert one row into the table. The warning indicates that there is an UNDERFLOW for the floating point value, and the value is turned into 0. Is this not the result you are seeing?

Because floating point is imprecise itself, we chose to map denormalized values to zero. Note that the values inserted into the table are actually 0, not the denormalized value. I understand that you might want to see an error, but such behavior would potentially break other existing applications.

Regards,
Jun
|||Hi Jun Fang,

According to your message you said that a warning(msg 168) is thrown when the transaction is happening, please kindly help us how to trap this warning message from the ADO.Net 2.0 (front end application) so that we can show the message and roll back the entire transaction for this kind of scenario.

Thanks