sql server错误将数据类型varchar转换为数字。 [英] sql server error converting data type varchar to numeric.
本文介绍了sql server错误将数据类型varchar转换为数字。的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
你好朋友......
i有以下查询。
声明 @ month int
声明 @ year int
set @ month = 8
set @ year = 2014
声明 @ startdate datetime
声明 @ enddate datetime
set @ startdate = CONVERT( datetime , convert ( varchar , @ year )+ ' - + convert( varchar , @ month )+ ' - ' + ' 01')
if (@ month> = MONTH(GETDATE())和 @ year> = YEAR(GETDATE ()))
开始
set @enddate =( select GETDATE())
end
else
开始
set @ enddate = DATEADD(day,-1 ,DATEADD(月, 1 , @ startdate ))
结束
创建 表 #suspended_alumina_gpl
(
edate datetime ,
avgliq 十进制( 18 , 5 )
)
while (@ startdate< = @ enddate)
开始
插入 进入 #suspended_alumina_gpl
选择 转换( datetime , @ startdate ),isnull(AVG(spent_liquor),' ')来自 suspended_alumina_gpl
其中 CONVERT ( varchar ,edatetime, 103 )= CONVERT( varchar , @ startdate )
set @startdate = DATEADD(day, 1 , @ startdate )
end
select * 来自 #suspended_alumina_gpl
但在执行时会抛出如下错误。
消息8114,等级16,状态5,行29
转换数据时出错将varchar输入到数字。
解决方案
您好b $ b
可能是问题在这里
isnull(AVG(spent_liquor),'')
如果AVG值为NULL,那么你正在尝试插入清空到十进制字段(avgliq),因此将其设置为0或其他一些数值。不要设置空字符。
所以使用如下: -
插入 进入 #suspended_alumina_gpl
选择 convert ( datetime , @ startdate ),isnull(AVG(spent_liquor) ), 0 )来自 suspended_alumina_gpl
其中 CONVERT ( varchar ,edatetime, 103 )= CONVERT( varchar , @ startdate )
希望它有所帮助。
如果你在数字字段中插入avg值然后使用
isnull(AVG(spent_liquor), 0 )
先生。 Karthik和Vinay很好地建议
但是如果你想在你的AVG(spent_liquor)为null时放'',那么只需更改你的
avgliq字段的数据类型临时表到FLOAT
问题解决了
hello friends...
i have following query.
declare @month int
declare @year int
set @month=8
set @year =2014
declare @startdate datetime
declare @enddate datetime
set @startdate=CONVERT(datetime,convert(varchar,@year)+'-'+convert(varchar,@month)+'-'+'01')
if (@month>= MONTH(GETDATE()) and @year>=YEAR(GETDATE()))
begin
set @enddate=(select GETDATE())
end
else
begin
set @enddate=DATEADD(day,-1,DATEADD(month,1,@startdate))
end
create table #suspended_alumina_gpl
(
edate datetime,
avgliq decimal(18,5)
)
while (@startdate<=@enddate)
begin
insert into #suspended_alumina_gpl
select convert(datetime,@startdate),isnull(AVG(spent_liquor),'') from suspended_alumina_gpl
where CONVERT(varchar,edatetime,103)=CONVERT(varchar,@startdate)
set @startdate=DATEADD(day,1,@startdate)
end
select * from #suspended_alumina_gpl
but it throws an error as follows while executing it.
Msg 8114, Level 16, State 5, Line 29
Error converting data type varchar to numeric.
解决方案
Hi
May be Issue is here
isnull(AVG(spent_liquor),'')
if the AVG value is NULL then you are trying insert Empty into decimal field (avgliq), So set it to "0" or some other numeric value. Do not set Empty character.
So use like below:-
insert into #suspended_alumina_gpl select convert(datetime,@startdate),isnull(AVG(spent_liquor),0) from suspended_alumina_gpl where CONVERT(varchar,edatetime,103)=CONVERT(varchar,@startdate)
hope it helps.
if you are inserting avg value in numeric field then use
isnull(AVG(spent_liquor),0)
Mr. Karthik and Vinay suggested well
but if u want to put '' when your AVG(spent_liquor) is null then just change the datatype of your
"avgliq" field in the temp table to "FLOAT"
problem solved
这篇关于sql server错误将数据类型varchar转换为数字。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文