如何在sql中将varchar转换为float / double？ [英] how to convert varchar to float /double in sql?

问题描述

如何将varchar转换为sql中的int / float？

how can i convert varchar to int/float in sql??

select id,
  sum (case when [Month] = 1 then aa else 0 end) January,
 sum(case when [Month] = 2 then aa else 0 end) February,
   sum(case when [Month] = 3 then aa  else 0 end) March ,
  sum(case when [Month] = 4 then aa else 0 end) April ,
sum(case when [Month] = 5 then aa  else 0 end) May ,
  sum(case when [Month] = 6 then aa else 0 end) June ,
  sum(case when [Month] = 7 then aa  else 0 end) July ,
 sum(case when [Month] = 8 then aa  else 0 end) August ,
  sum(case when [Month] = 9 then aa  else 0 end) September ,
 sum(case when [Month] = 10 then aa  else 0 end) October ,
 sum(case when [Month] = 11 then aa  else 0 end) November ,
 sum(case when [Month] = 12 then aa  else 0 end) December
from order
group by id

当我执行上面的代码时，我收到错误'转换varchar值时转换失败'310.2 '到数据类型int。'

我该如何解决这个问题？请帮助我。

感谢高级：）

when i execute the code above, i got error 'Conversion failed when converting the varchar value '310.2' to data type int.'
how can i solve this problem? please help me.
thanks in advanced :)

推荐答案

您会看到SQLs自动转换的情况。在同一个命令中，你有 aa 和 0 ，所以SQL尝试将它们带到相同的类型。最后一个类型由最后一个元素决定，在你的情况下是0，一个整数。但是 aa 不是整数（并且某处值为310.2）因此SQL无法转换。将0更改为0.0以解决您的问题...

You see the case of SQLs auto-convert. On the same command you have aa and 0, so SQL try to bring them to the same type. The final type is decided by the last element which is 0 in your case, an integer. However aa is not integer (and somewhere has a value of 310.2) so SQL fails to convert. Change 0 to 0.0 to solve your problem...

sum (case when [Month] = 1 then aa else 0.0 end) January,

既然你已经告诉你在你的应用程序中需要'aa'作为字符串，你为什么不发送这两个值作为字符串



总和（[月] = 1然后其他'0'结束时的情况）1月，

Since you have told that you need 'aa' as string in your application, why don't you send both values as string

sum (case when [Month] = 1 then aa else '0' end) January,

这篇关于如何在sql中将varchar转换为float / double？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！