Text to numeric in a query 1

[Terris]

two fields fmoldval and fmnewval table field defined as
text. Would like to use them both in a mathmatical equation
fmnewval - fmoldval

actual data is stored in a decimal(9,5)
Tried both cast as decimal (9,5) and cast as int
and received the error message
Explicit conversion from data type text to int (or decimal) is not allowed

anyway to convert/cast these fields datatype in the select so I can use them

 

[KraGiE]

Cast it to Float. That's like an end all cure all.

.NET Developer/Contracting Consultant.
<a href=&quot;

http://www.inklass.com&quot;>InKlaSs.com</a>

 

[Terris]

did not work
Explicit conversion from data type text to float is not allowed.

 

[SQLSister]

text data types can only be converted to a char data type. Why are you storing numbers in text datatype anyway? This is a datatype reserved for large amounts of text which won't fit inthe max size of one of the char types of fields (more than 8000 bits of information)

 

[Terris]

I don't have alot of freedom with the database. I am writing custom reports off our ERP systems database developed by the software provider. I was however able to resolve this problem by doing a cast of a cast
CAST( CAST( fieldname AS VARCHAR(17) ) AS FLOAT )