CODEWITHSUNDEEP
sqlsql-servert-sqlexceptioncasting
Ashkan
Ashkan

Reputation: 1673

Cannot cast nvarchar to float

I have a weird problem.

In my query I select 5 columns which two of them are nvarchar in the form of numeric (only contains number and precision point), and other three are uniqueIdentifier and nvarchar.

I want to cast these two columns to Float in my select statement but I'm getting an error

Cannot cast nvarchar to float.

I checked the format of all these values many many times. Trust me, they are fine.

But when I just select these two columns and cast them to float the query runs successfully.

I appreciate any help on this subject.

I can paste the query here too. but the whole query is more then 100 lines and this will be frustrating to write and read!

Upvotes: 1

Views: 31781

Answers (2)

rhoadsce
rhoadsce

Reputation: 278

Definitely going to need more info from you before answering. Can you post some of your code (or a small reproduction of your issue)? Perhaps your table definition as well?

Since all of the values are numeric, why leave the column as an nvarchar?

Finally, does any of your data contain a dollar sign ($)?

This works:

DECLARE @Text nvarchar(100)

SET @Text = '1234.567'

SELECT CONVERT(float, @Text) AS ColumnValue

So does this:

DECLARE @Text nvarchar(100)

SET @Text = '    1234.567    '

SELECT CONVERT(float, @Text) AS ColumnValue

But this does not:

DECLARE @Text nvarchar(100)

SET @Text = '$1234.567'

SELECT CONVERT(float, @Text) AS ColumnValue

Upvotes: 3

Geek
Geek

Reputation: 429

Not sure without seeing your query, but I think this should work:

SELECT CONVERT(Float(2), LTRIM(RTRIM(<nVarchar Column>))) AS Amount FROM ......

Upvotes: 0

Related Questions