Question

将varchar值转换为数据类型为int的列时发生语法错误

MSSQL数据库一存储过程：

CREATE PROCEDURE post_time
@tyear varchar(4)='',
@tmonth varchar(2)='',
@tday varchar(2)='',
@thour varchar(2)='',
@tminute varchar(2)='',
@tsecond varchar(2)=''
As

declare @time datetime
set @time=getdate()
declare @b varchar(30)
declare @year varchar(4)
declare @month varchar(2)
declare @day varchar(2)
declare @hour varchar(2)
declare @minute varchar(2)
declare @second varchar(2)
declare @millisecond varchar(4)

set @year=(Case When @tyear='' then Cast(year(@time) as varchar(4)) else @tyear end)
set @month=(Case When @tmonth='' then Cast(month(@time) as varchar(2)) else @tmonth end)
set @day=(Case When @tday='' then Cast(day(@time) as varchar(2)) else @tday end)
set @hour=(Case When @thour='' then Cast(datepart(hh,@time) as varchar(2)) else @thour end)
set @minute=(Case When @tminute='' then Cast(datepart(mi,@time) as varchar(2)) else @tminute end)
set @second=(Case When @tsecond='' then Cast(datepart(ss,@time) as varchar(2)) else @tsecond end)
set @millisecond=Cast(datepart(ms,@time) as varchar(3))

set @b=''+@year+'-'+@month+'-'+@day+' '+@hour+':'+@minute+':'+@second+'.'+@millisecond+''
return @b
GO

执行execute post_time '2001','03','11','03','23','03' 提示如下错误：
“将 varchar 值 '2001-03-11 03:23:03.420' 转换为数据类型为 int 的列时发生语法错误。”

请问如何解决这个问题呢？
出错的行是第30行，即 return @b

Answer 1

如果试图进行不可能的转换（例如，将含有字母的
char
表达式转换为
int
类型），SQL
Server
将显示一条错误信息。
含了“，”
SQL认为是字母，给出了错误信息
如需要使用该查询，可以将articles
字段内的非数字和+-号.的符号都删除

Answer 2

先问问你这个存储过程想实现什么功能？

'2001-03-11 03:23:03.420' 这样的格式的字符怎么能转INT啊

你这个根本就实现什么功能嘛
帮你改了以句 可以执行了
alter PROCEDURE post_time
@tyear varchar(4),
@tmonth varchar(2),
@tday varchar(2),
@thour varchar(2),
@tminute varchar(2),
@tsecond varchar(2)
As

declare @time datetime
set @time=getdate()
declare @b varchar(30)
declare @year varchar(4)
declare @month varchar(2)
declare @day varchar(2)
declare @hour varchar(2)
declare @minute varchar(2)
declare @second varchar(2)
declare @millisecond varchar(4)

set @year=(Case When @tyear='' then Cast(year(@time) as varchar(4)) else @tyear end)
set @month=(Case When @tmonth='' then Cast(month(@time) as varchar(2)) else @tmonth end)
set @day=(Case When @tday='' then Cast(day(@time) as varchar(2)) else @tday end)
set @hour=(Case When @thour='' then Cast(datepart(hh,@time) as varchar(2)) else @thour end)
set @minute=(Case When @tminute='' then Cast(datepart(mi,@time) as varchar(2)) else @tminute end)
set @second=(Case When @tsecond='' then Cast(datepart(ss,@time) as varchar(2)) else @tsecond end)
set @millisecond=Cast(datepart(ms,@time) as varchar(3))

set @b=''+@year+'-'+@month+'-'+@day+' '+@hour+':'+@minute+':'+@second+'.'+@millisecond+''
print @b
GO

execute post_time '2001','03','11','03','23','03'

Answer 3

varchar是字符型，int是整型数值型，'admin
'是字符串，你要把字符串转换成int当然出错

Answer 4

select
*
from
articles
where
pid
in
(36,37,43,20,16,17,34,116,117,119)
and
cfrom=49
and
shenhe
=1
order
by
dateandtime
desc
句中使用的字段有3：pid
(估计楼主已经看过时int型），cfrom和shenhe怕是出错的根源，检查是否int性。