duan1396
2014-05-28 03:56
浏览 466
已采纳

将Varchar从变量字符串转换为Float并插入SQL Server?

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?

图片转代码服务由CSDN问答提供 功能建议

我是SQL Server新手。 我创建了一个脚本,我导入数据并插入SQL Server。 更新查询工作正常但插入查询不正常。 我收到错误

将数据类型varchar转换为float时出错。插入到dbo。

这是代码

  $ amount = trim(str_replace('$','',$ data [2])); 
 // echo($ amount);  //打印1,000,000.00 
 
 // INSERT QUERY 
 //尝试1:失败
 
 $ Query =“INSERT INTO dbo.testtable(id,name,amount)
 values(11,'John',  $ amount)“; 
 
 //尝试2:失败
 
 $ Query =”INSERT INTO dbo.testtable(id,name,amount)
 values(11,'John',CONVERT(FLOAT,'  $ amount'))“; 
   
 
 

如何在世界中从变量( $ amount )中插入适当的浮点值到 SQL Server?

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • dongshang6062 2014-05-28 04:09
    已采纳

    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 
    
    已采纳该答案
    打赏 评论

相关推荐 更多相似问题