Error converting data type nvarchar to float? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Error converting data type nvarchar to float?

Author

Topic

cdik88
Starting Member

2 Posts

Posted - 2011-09-20 : 02:26:27

I need to download data from one database to another database, so i am using this query:

INSERT INTO GSC2.DBO.salebill
SELECT *FROM VGP3.DBO.salebill

then it gives a error msg as:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to float.

can someone help me with this problem???

c_

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-20 : 02:32:31

It looks like you have a character field (nvarchar) that is not numeric in table VGP3.dbo.salebill that you're trying to insert into a numeric (float) field in GSC2.dbo.salebill.

cdik88
Starting Member

2 Posts

Posted - 2011-09-20 : 02:35:51

yes i know it...then u know query to convert it to float~

c_

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-20 : 02:56:26

Do you know which column it is? What data is in the character string? If it's not numeric, it won't convert, so you'd have to exclude those rows.

You can do a straight conversion like this: convert(float, yourcolumn), but it's going to fail like I said if there are characters.

Check out this post, very good explanation of some of these concept:

http://sqlinthewild.co.za/index.php/2011/07/26/goodbye-isnumeric-hell/

Kristen
Test

22859 Posts

Posted - 2011-09-20 : 03:06:33

This should help find it:


SELECT Col1, Col2, ...
FROM MyTable
WHERE LTrim(RTrim(MyVarcharColumn)) LIKE '[-+0-9.]%[^0-9.]%'
      OR LTrim(RTrim(MyVarcharColumn)) NOT LIKE '[-+0-9.]%'
      OR LTrim(RTrim(MyVarcharColumn)) LIKE '%.%.%'
      OR IsNumeric(MyVarcharColumn) = 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 03:10:51

why storing numeric data in nvarchar field? also if it has some non numeric data also along with this, your convertion will fail. first see if you've some nonnumeric data present in nvarchar field using a where like


WHERE [NVarcharFieldnamehere] LIKE '%[^0-9]%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-09-20 : 03:13:03

"INSERT INTO GSC2.DBO.salebill
SELECT * FROM VGP3.DBO.salebill"

Because you are using SELECT *, and no column list on the INSERT, maybe the columns are in different sequence on each database?

Do the columns "line up" in this query?


SELECT TOP 1 * FROM GSC2.DBO.salebill
SELECT TOP 1 * FROM VGP3.DBO.salebill

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources