Convert Varchar to Float from a variable string and insert into SQL Server?

Question

I am new to SQL Server. I have created a script where I import data and insert into SQL Server. The update query works fine but the insert query does not . I get a error

Error converting data type varchar to float.. INSERT INTO dbo.

This is the code

 $amount = trim(str_replace('$','',$data[2]));
// echo ($amount); // prints 1,000,000.00

// INSERT QUERY
//Try 1 : Fails

  $Query  = "INSERT INTO dbo.testtable (id, name, amount) 
                                values ( 11, 'John' , $amount )";

  //Try 2 : Fails

  $Query  = "INSERT INTO dbo.testtable (id, name, amount) 
                                values ( 11, 'John' ,  CONVERT(FLOAT,'$amount') )";

How in the world can I insert a proper float value from the variable ($amount) into SQL Server?

there is no comma, u mean single quotes? i did but no change.Still error :CONVERT(FLOAT,$amount) -->[Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near ','.. — user3054077
– user3054077, Commented May 28, 2014 at 4:01

zhongxiao37 · Accepted Answer · 2014-05-28 04:41:06Z

1

use this:

cast('$amount' as money)

Updates: It actually depends on what's the type of your column amount. If it's varchar, it should not throw an error for 1st line. So, I guess it's something like decimal(18, 2). Refer to the demo here

declare @amount varchar(25)
SET @amount = '1,000.00'

create table #tmp_money (amount FLOAT)
insert into #tmp_money
SELECT cast(@amount as money)

select * from #tmp_money

edited May 28, 2014 at 4:41

answered May 28, 2014 at 4:09

zhongxiao37

9878 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

user3054077 Over a year ago

its of type float in db

zhongxiao37 Over a year ago

I think my answer works for FLOAT. You could check the demo link above.

user3054077 Over a year ago

i am not sure where r u asking me to declare @amount varchar(25)? in the php code?

zhongxiao37 Over a year ago

No, simply change CONVERT(FLOAT,'$amount') to cast(@amount as money). The link I post above is for testing purpose to demo why my suggestion works.

user3054077 Over a year ago

[Microsoft][SQL Server Native Client 10.0][SQL Server]Must declare the scalar variable "@amount".. INSERT INTO dbo.

|

Collectives™ on Stack Overflow

Convert Varchar to Float from a variable string and insert into SQL Server?

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related