DataType converting, char to float

I need to convert Data Types in a table where all fields are char, so that the ones containing numeric values is decimal or float.
I've tried this query:
SELECT Ansilumens, CAST(RTRIM(Ansilumens) AS FLOAT) FROM dbo.projector
And I get the error: [Microsoft] [ODBC SQL Server Driver] [SQL Server] Error converting data type varchar to float.

Does anyone how I can convert chars to decimals or floats? When the tabel already contains lots of data, without loosing it..

Thanks.--Find bad values
select YourCol
from YourTable
where isnumeric(YourCol)=0

/*
Then choose, what numeric type do you want.
Float is usually used in science for storing inaccurate very high range values. Stored as single/double real binary.
Int-like datatype for integers (without decimal places). Stored as sign fixed binary.
Numeric for precise calculations, large nums (10^36), fixed decimals. Stored as sign nibble (2 decimal digits in one byte)
rounded up to 1+4n bytes.
Money is fast predefined numeric with special rounding and 4 decimal places. Stored as sign fixed binary.
*/

--if your nums are really large, try FLOAT(53) or NUMERIC(38) or ballanced NUMERIC(32,16)

--Sending test values would be your benefit.|||If you plan on using numeric or decimal data types you really have to know what is the largest numeric value you will use - not just to the left of the decimal but to the right as well (your precision and scale) otherwise you will receive an arithmetic overflow error. Float has the same issues with precision.

The following post discusses this problem:

post (http://dbforums.com/showthread.php?threadid=554550)|||Well, my problem (after some more testing) seems that everything I try to insert from VB6 is impossible to get into the db unless the datatype in the db is char, varchar, timestamp or text that is.
This is the code:

Dim InsertQuery As String
Set oConn = New Connection

Set oRec = New Recordset
oRec.Open InsertQuery, oConn

is my problem related to that everything in InsertQuery, is of course, a string when the SQL query executes?

-jr|||You need to post an actual insert statement and the data types as defined by the table.|||InsertQuery = "INSERT INTO black (Part, Serial, Time, S0) VALUES ('101-0001-00', '12345678', '" & Now & "', '432.95');"

Is a test-query I use

And the table is defined as follows:
Part - char - size 11 - Do not allow nulls
Serial - char - size 8 - Do not allow nulls
[Time] - datetime - Do not allow nulls
S0 - decimal - Precision: 2 - Do not allow nulls|||Your post has 2 main problems
1. You do not specify scale for decimal. Minimum numeric(5,2) for this insert.
2. You use VB Now() function, which is setting-specific. Use getdate() on server.

create table testNum(
Part char (11) not null
,Serial char(8) not null
,[Time] datetime not null
,S0 decimal(15,2) not null
)
GO
INSERT INTO testNum (Part, Serial, Time, S0) VALUES ('101-0001-00', '12345678', getdate(), '432.95')
--faster
INSERT INTO testNum (Part, Serial, Time, S0) VALUES ('101-0001-00', '12345678', getdate(), 432.95)|||What is the most numbers to the left of the decimal and to the right for the column S0 ? Once you know this, create that field with a precision of the 2 maximums combined and the scale of the maximum of the length to the right of the decimal.|||Thanks rnealejr and ispaleny... it was clearly my SQL knowledge (or the lack of it) that was the problem. Works great now, though. Thanks again.|||Just one more thing..
When the value is (e.g) 2300.00 the .00 is not showing in the db, how to make the decimals show even though they are only zeroes (0)?|||This will happen in enterprise manager - run a query in query analyzer and you will see them.

Datatype convert char to numeric

Hi,

I read the topic from JROdden and this case is similiar but...

I got several varchar fields with
values like
1.2
1.3
... these I can covert with
select CONVERT(dec(5,2), fieldname) as fieldname

In fact I also solved undefined- and NULL-values with.
CONVERT(decimal(12, 2), CASE WHEN GESCHKOSTMAX IS NULL OR
GESCHKOSTMAX < '0' THEN '0' ELSE GESCHKOSTMAX END) as GESCHKOSTMAX,

But now there are values like
1,4 and these ones neither CONVERT nor CAST will handle.

I tried the
SELECT DISTINCT KMPAUSCHALE
FROM extr_INTFIRMA
WHERE (isnumeric(KMPAUSCHALE) = 1)

and get
0,40
0.25
0.30 and so on...

The error is:
[Microsoft][ODBC SQL Driver][SQL Server]Error converting datatype varchar to decimal. (or float or numeric (whatever I tried))

I think the easiest way would be to insist on higher data quality but
I also would like to solve this interesting challenge.

Thanks for any hints

By the way, I followed rudys link to
http://rudy.ca/afdb.html
and now I know how I could protect myself !!!!

There must be a voice in my head saying:
Try the db-forum, try it and stay happy... ;-)

best regards and have fun with new year eve.

MichaelWhy are you worried about < 0? And how do you now they will translate to 5,2?

You should be more worried about other chars that don't translate...

SELECT CONVERT(float,ISNULL(REPLACE(GESCHKOSTMAX,',',',') ,0))
FROM extr_INTFIRMA
WHERE ISNUMERIC(REPLACE(GESCHKOSTMAX,',',','))=1

My question to you is...what do you do with the data that doesn't fit this profile?

You're exclusing an entire population of potentially valid data...

Yes you should more tightly define the columns datatype...

It's probably going to require data cleaning though...|||Originally posted by Michael Kaiser
By the way, I followed rudys link to
http://rudy.ca/afdb.html
and now I know how I could protect myself !!!!

:cool:|||Originally posted by r937
:cool:

Hi Brett,

thank you very much for that SQL-command.
I tried
SELECT CONVERT(decimal(15, 4), ISNULL(REPLACE(HERUMSATZWELT, ',', '.'), 0)) AS Expr1
FROM extr_INTFIRMA
WHERE (ISNUMERIC(REPLACE(HERUMSATZWELT, ',', '.')) = 1)

(please note the point I replaced) and it worked well.

Nevertheless there are some field which do not contain NULLS.
The ASCII(fieldname) gives me NULL as result.
So I guess this field is "not defined".

This is the reason why I' m worried about < 0.
All "undefined" went to "0".
Not very elegant but it worked...

best regards

Michael|||Originally posted by Michael Kaiser
Nevertheless there are some field which do not contain NULLS.
The ASCII(fieldname) gives me NULL as result.
So I guess this field is "not defined".

This is the reason why I' m worried about < 0.
All "undefined" went to "0".
Not very elegant but it worked...

best regards

Michael

I don't understand...ISNULL() Will only give you a 0 if the value is NULL

And what is ASCII(fieldname) for?

And I don't understand "undefined"

But hey, as long as it work

Ihr Willkommen|||Hi Brett,

the data are coming from a web-frontend into an access-db.
Obviosly the user don't have to fillout all questions and therefore
some fields remain empty.
Others change to "NULL".
I don't know why.
I imported the data to SQL server and
checked them with select distinct
I get two "empty" fields in the result.
One contains "NULL" the other "nothing" ?
I tried ASCII(fieldname) to search for "nonvisible" data but
this ASCII-command results in NULL.

When trying your command it fails because of this empty fields.

I checked the SQL online help and found the
NonEmpty function but that will not work within a sql-command
(in my view)

Whatsoever...
now it is time to go to a new years eve party...

I will try it again next year!!!

By the way...

your welcome = Ihr Willkommen
is very, very strange - hihi -

your welcome best fits in german - gern geschehen -

But I get the idea.

Thanks for your help and tons of fun the next hours...

Michael|||und einen guten rutsch ins neue jahr !!

rudy|||Hey, that's what I get for a google translation...

anyway...happy new year!

And what you're describing is an empty string, which is not null

SELECT COUNT(*)
FROM extr_INTFIRMA
WHERE GESCHKOSTMAX = ''

Should show you how many

Have a liter or 2 for me!|||Rudy - I tried selling my version of that device but no luck. I have been wearing mine for several years now - and I feel so much safer today - no aliens talking in my head anymore ... :-)

Thanks for posting that - I have not seen that for a couple of years now and it always makes me laugh.

Datatype

I am trying to convert a NVCHAR datatype to a DATETIME data type is that possible?Only if it's a valid date. Post a simple example of failure along with associated code and data values and let's see what we can do.|||DECLARE @.x TABLE (Col1 nvarchar(25))
INSERT INTO @.x(Col1)
SELECT '12/31/2004 11:59:59' UNION ALL
SELECT 0 UNION ALL
SELECT 'Happy New Year'

SELECT CONVERT(datetime,Col1) FROM @.x WHERE ISDATE(Col1) = 1|||Here is the select statement I am using but when I use it it doesnt just give me the 2004 only it gives me everything in the table
SELECT TM#, LastName, FirstName, [Date]
FROM Revocations_Tbl
WHERE ([Date] BETWEEN '01/01/2004' AND '12/31/2004')

this is what the tables design looks like all the other tables have DATETIME for their datatypes and I have no problems, when I tried to convert the datatype I got alot of error messages|||To be honest...I'd fix the table...

ALSO! Anyone figure out why I'm getting the conversion error in the following...

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myRevocations_Tbl99(TM# int, LastName nvarchar(25), FirstName nvarchar(25), [Date]nvarchar(10))
GO

INSERT INTO myRevocations_Tbl99 (TM#, LastName, FirstName, [Date])
SELECT 1, 'Kaiser', 'Brett', '10/24/2004' UNION ALL
SELECT 2, 'Kaiser', 'Brett', '01/01/2005' UNION ALL
SELECT 3, 'Kaiser', 'Brett', '12/31/2003' UNION ALL
SELECT 4, 'Desiree', 'A', '01/01/2004' UNION ALL
SELECT 5, 'Desiree', 'Whos', '10/19/2004' UNION ALL
SELECT 6, 'Desiree', 'Your', '10/19/200a' UNION ALL
SELECT 7, 'Desiree', 'Their', '12/31/2004' UNION ALL
SELECT 8, 'A Row', 'Of Data 1', 'aaaaaaaaaa' UNION ALL
SELECT 9, 'A Row', 'Of Data 2', '0' UNION ALL
SELECT 0, 'A Row', 'Of Data 3', '123456789'
GO

-- Your's

DECLARE @.x varchar(10), @.y varchar(10)
SELECT @.x = '01/01/2004', @.y = '12/31/2004'
SELECT TM#, LastName, FirstName, [Date]
FROM myRevocations_Tbl99
WHERE [Date] BETWEEN @.x AND @.y
GO

-- Mine

DECLARE @.x datetime, @.y datetime
SELECT @.x = '01/01/2004', @.y = '12/31/2004'
SELECT TM#, LastName, FirstName, [Date]
FROM (SELECT TM#, LastName, FirstName, [Date]
FROM myRevocations_Tbl99
WHERE ISDATE([Date])=1) AS XXX
WHERE DATEDIFF(yy,CONVERT(datetime,[Date]),@.x) = 0
AND DATEDIFF(yy,CONVERT(datetime,[Date]),@.y) = 0
GO

SET NOCOUNT OFF
DROP TABLE myRevocations_Tbl99
GO|||ALSO! Anyone figure out why I'm getting the conversion error in the following...

It looks like the optimizer is flattening out your query into a single table scan, eliminating the ISDATE, then choking on the strings that aren't dates. At least that's what the estimated plan indicates.
Seems like an odd thing to do.|||Put the row for [TM#] 8 first, then it will work.

-PatP|||Put the row for [TM#] 8 first, then it will work.

-PatP

Like I'd have any control over the data...|||Well actually Pat, It blows up right away...|||This works...

DECLARE @.xxx table (TM# int, LastName nvarchar(25), FirstName nvarchar(25), [Date]nvarchar(10))

INSERT INTO @.xxx (TM#, LastName, FirstName, [Date])
SELECT TM#, LastName, FirstName, [Date]
FROM myRevocations_Tbl99
WHERE ISDATE([Date])=1

DECLARE @.x datetime, @.y datetime
SELECT @.x = '01/01/2004', @.y = '12/31/2004'
SELECT TM#, LastName, FirstName, [Date]
FROM @.xxx
WHERE DATEDIFF(yy,CONVERT(datetime,[Date]),@.x) = 0
AND DATEDIFF(yy,CONVERT(datetime,[Date]),@.y) = 0
GO

Datareader, DBNull and ? Operator

Hi!

I was trying to use the ?? operator with an DBDatareader. eg:

long lValue = Convert.ToInt32(objReader["ParentID"] ?? -1);

which throws the following exception:"... Object cannot be cast from DBNull to other types."
I think the reason is, that DBNull.Value isn′t actually null. Therefore I changed my code to:

long lValue = objReader["ParentID"] != DBNull.Value ? Convert.ToInt32(objReader["ParentID"]) : -1;

Is there a better way of doing this ? Can one use the ?? operator with a Datareader anyway?

Thanks for your help!

PS: sorry for my poor english.

Hi,

the ?? operator is used in conjunction with nullable types. The way you wrote it using the ?: operator is just fine.

Grz, Kris.

|||

Thanks a lot!

dataflow to excel - Convert numbers stored as text to numbers Excel Cell Error

I'm trying to write data to excel from an ssis component to a excel destination.

Even thought I'm writing numerics, every cell gets this error with a green tag:

Convert numbers stored as text to numbers

Excel Cells were all pre-formated to accounting 2 decimal, and if i manually type the exact data Im sending it formats just fine.

I'm hearing this a common problem -

On another project I was able to find a workaround for the web based version of excel, by writing this to the top of the file:

<style>.text { mso-number-format:\@.; } </style>

is there anything I can pre-set in excel (cells are already formated) or write to my file so that numerics are seen as numerics and not text.

Maybe some setting in my write drivers - using sql servers excel destination.

So close.. Thanks for any help or information.

You received some sugestion on your other thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1392693&SiteID=1

Opening new threads for same problem makes more dificult to help you.