CREATE PROCEDURE ShowDZData
@ProsureDate varchar(1),
@beginDatetime datetime,
@endDatetime datetime,
@typetxt varchar(1),
@danNo varchar(22),
@depotid varchar(8)
ASdeclare @sqltxt varchar(4000)
declare @where varchar(4000) begin
set @sqltxt = ' SELECT dbo.c_ManagerAccounts.AccountsID, dbo.c_ManagerAccountsSub.depotid, dbo.c_ManagerAccountsSub.Type, dbo.c_ManagerAccountsSub.Content, dbo.c_ManagerAccountsSub.Price, dbo.c_ManagerAccounts.PayDatetime,
dbo.c_ManagerAccounts.ProcDatetime, dbo.c_ManagerAccounts.chkDate, dbo.c_ManagerAccounts.chkname, dbo.c_ManagerAccounts.SurDate, dbo.c_ManagerAccounts.Surname, dbo.c_ManagerAccounts.Proname
FROM dbo.c_ManagerAccounts RIGHT OUTER JOIN dbo.c_ManagerAccountsSub ON dbo.c_ManagerAccounts.AccountsID = dbo.c_ManagerAccountsSub.AccountsID '
if (@danNo='0') ------单号为空的时候不加单号条件
begin
if (@ProsureDate='0') ----制单时间
begin
set @where = ' Where dbo.c_ManagerAccounts.ProcDatetime between ('+ @beginDatetime+ ' ) and ( '+ @endDatetime +') and dbo.c_ManagerAccountsSub.depotid=' + @depotid
end
else if(@ProsureDate='1') --审核时间
begin
set @where = ' Where dbo.c_ManagerAccounts.SurDate between ' + @beginDatetime + ' and ' + @endDatetime + ' and dbo.c_ManagerAccountsSub.depotid=' + @depotid
end
end
else --------加上单号
begin
if (@ProsureDate='0')
begin
set @where = ' Where dbo.c_ManagerAccounts.ProcDatetime between ' + @beginDatetime + ' and ' + @endDatetime + ' and dbo.c_ManagerAccountsSub.depotid=' + @depotid +' and dbo.c_ManagerAccounts.AccountsID='+@danNo
end
else if(@ProsureDate='1')
begin
set @where =' Where dbo.c_ManagerAccounts.SurDate between ' + @beginDatetime + ' and '+ @endDatetime + ' and dbo.c_ManagerAccountsSub.depotid='+@depotid+ ' and dbo.c_ManagerAccounts.AccountsID='+@danNo
end
end ----------加上判断类型 if (@typetxt='1')
begin
set @where=@where+ ' and dbo.c_ManagerAccounts.chksure=0'
end
else if(@typetxt='2')
begin
set @where=@where+' and dbo.c_ManagerAccounts.chksure=1 and dbo.c_ManagerAccounts.sure =0'
end
else if(@typetxt='3')
begin
set @where=@where + ' and dbo.c_ManagerAccounts.sure =1'
end set @sqltxt= @sqltxt + @where
end
exec sp_executesql @sqltxt
GO
注意红色部分,就是在调到between 与 and 都报错,提示:从字符串转换为 datetime 时发生语法错误。,可是我前台传的是日期格式啊, 我调了好几个小时参考网上的别人的问题,都没有解决办法,我该怎么办?求助你们了。...高人帮帮我吧...
@ProsureDate varchar(1),
@beginDatetime datetime,
@endDatetime datetime,
@typetxt varchar(1),
@danNo varchar(22),
@depotid varchar(8)
ASdeclare @sqltxt varchar(4000)
declare @where varchar(4000) begin
set @sqltxt = ' SELECT dbo.c_ManagerAccounts.AccountsID, dbo.c_ManagerAccountsSub.depotid, dbo.c_ManagerAccountsSub.Type, dbo.c_ManagerAccountsSub.Content, dbo.c_ManagerAccountsSub.Price, dbo.c_ManagerAccounts.PayDatetime,
dbo.c_ManagerAccounts.ProcDatetime, dbo.c_ManagerAccounts.chkDate, dbo.c_ManagerAccounts.chkname, dbo.c_ManagerAccounts.SurDate, dbo.c_ManagerAccounts.Surname, dbo.c_ManagerAccounts.Proname
FROM dbo.c_ManagerAccounts RIGHT OUTER JOIN dbo.c_ManagerAccountsSub ON dbo.c_ManagerAccounts.AccountsID = dbo.c_ManagerAccountsSub.AccountsID '
if (@danNo='0') ------单号为空的时候不加单号条件
begin
if (@ProsureDate='0') ----制单时间
begin
set @where = ' Where dbo.c_ManagerAccounts.ProcDatetime between ('+ @beginDatetime+ ' ) and ( '+ @endDatetime +') and dbo.c_ManagerAccountsSub.depotid=' + @depotid
end
else if(@ProsureDate='1') --审核时间
begin
set @where = ' Where dbo.c_ManagerAccounts.SurDate between ' + @beginDatetime + ' and ' + @endDatetime + ' and dbo.c_ManagerAccountsSub.depotid=' + @depotid
end
end
else --------加上单号
begin
if (@ProsureDate='0')
begin
set @where = ' Where dbo.c_ManagerAccounts.ProcDatetime between ' + @beginDatetime + ' and ' + @endDatetime + ' and dbo.c_ManagerAccountsSub.depotid=' + @depotid +' and dbo.c_ManagerAccounts.AccountsID='+@danNo
end
else if(@ProsureDate='1')
begin
set @where =' Where dbo.c_ManagerAccounts.SurDate between ' + @beginDatetime + ' and '+ @endDatetime + ' and dbo.c_ManagerAccountsSub.depotid='+@depotid+ ' and dbo.c_ManagerAccounts.AccountsID='+@danNo
end
end ----------加上判断类型 if (@typetxt='1')
begin
set @where=@where+ ' and dbo.c_ManagerAccounts.chksure=0'
end
else if(@typetxt='2')
begin
set @where=@where+' and dbo.c_ManagerAccounts.chksure=1 and dbo.c_ManagerAccounts.sure =0'
end
else if(@typetxt='3')
begin
set @where=@where + ' and dbo.c_ManagerAccounts.sure =1'
end set @sqltxt= @sqltxt + @where
end
exec sp_executesql @sqltxt
GO
注意红色部分,就是在调到between 与 and 都报错,提示:从字符串转换为 datetime 时发生语法错误。,可是我前台传的是日期格式啊, 我调了好几个小时参考网上的别人的问题,都没有解决办法,我该怎么办?求助你们了。...高人帮帮我吧...
输出看看sql
是什么类型?
是什么类型? 当然是日期类型啊.
set @where = ' Where dbo.c_ManagerAccounts.ProcDatetime between ('+ @beginDatetime+ ' ) and ( '+ @endDatetime +')
insert into tb select 1,'2009-06-25' union all select 2,'2009-06-27' union all select 3,'2009-06-29' union all select 4,'2009-06-30' union all select 5,'2009-07-01'
go
declare @dt1 datetime,@dt2 datetime,@sqlstr varchar(800)
set @dt1='2009-06-27'
set @dt2='2009-06-30'
set @sqlstr='select * from tb where dt between '''+convert(varchar,@dt1,120)+''' and ''' +convert(varchar,@dt2,120)+''''
execute(@sqlstr)
go
--or
declare @dt1 varchar(10),@dt2 varchar(10),@sqlstr varchar(800)
set @dt1='2009-06-27'
set @dt2='2009-06-30'
set @sqlstr='select * from tb where dt between '''+@dt1+''' and ''' +@dt2+''''
execute(@sqlstr)
go
drop table tb
/*
id dt
----------- -----------------------
2 2009-06-27 00:00:00.000
3 2009-06-29 00:00:00.000
4 2009-06-30 00:00:00.000(3 行受影响)id dt
----------- -----------------------
2 2009-06-27 00:00:00.000
3 2009-06-29 00:00:00.000
4 2009-06-30 00:00:00.000(3 行受影响)*/
对对,问题就出在这,参数是日期形式,在拼接的是报错
,看下面的测试,所以把参数改成字符串形式
declare @t datetime
set @t=getdate()
select '现在是'+@t
/*
服务器: 消息 241,级别 16,状态 1,行 3
从字符串转换为 datetime 时发生语法错误。*/
declare @strSQL nvarchar(1024)
declare @beginDatetime datetime
declare @endDatetime datetimeset @beginDateTime = '2008-11-11'
set @endDatetime = '2010-01-01'set @strSQL = 'select * from dbo.sellA where 日期 between '''+
Convert(varchar,@beginDateTime,120)+
''' and '''+ Convert(varchar,@endDateTime,120)+
''''print @strSQLexec sp_executesql @strsql
set @where = ' Where (dbo.c_ManagerAccounts.ProcDatetime between '''+
Convert(varchar,@beginDateTime,120)+
''' and '''+ Convert(varchar,@endDateTime,120)+
''') and dbo.c_ManagerAccountsSub.depotid= ' + @depotid-- Where (dbo.c_ManagerAccounts.ProcDatetime between '2008-11-11 00:00:00' and '2010-01-01 00:00:00') and dbo.c_ManagerAccountsSub.depotid= 1