--------------------------------存储过程
ALTER proc [dbo].[proc_OA_FileSearch]
@filename varchar(100),
@accessoryname varchar(100),
@username varchar(100),
@pageindex int,
@pagesize int,
@begintime datetime,
@endtime datetime
as
declare @sqlstr varchar(500)
set @sqlstr='with newtable as (select * ,
ROW_NUMBER() OVER(order by fileid) as RowNumber
from FileInfo where IfDelete=0'
if(@filename<>'')
begin
set @[email protected]+' and FileName like [email protected] +'%'+''' '
end
if(@accessoryname<>'')
begin
set @[email protected]+' and FileId in (select FileId from AccessoryFile where AccessoryName like [email protected]+'%'+''''+')'
end
if(@username<>'')
begin
set @[email protected]+' and FileOwner in (select UserId from UserInfo where UserName like [email protected]+'%'+ ''''+ ')'
end
if(@begintime<>'' and @endtime<>'')
begin
set @[email protected]+'and CreateDate between [email protected] +''''+ ' and '''+ @endtime +''''
end
set @[email protected]+' )select * from newtable where RowNumber between '+
CAST((@pageindex-1)[email protected]+1 as varchar(100))+' and '+ CAST(@[email protected] as varchar(100))
exec(@sqlstr)
--------------------------------------------------------------------
1在数据库中测试 exec proc_OA_FileSearch '','','',1,10,'1900-1-1','2010-1-1' 没什么问题
2拿到界面测试的时候 填上日期的时候 总提示：从字符串向datetime转换时失败,查不出结果
3界面的日期格式没问题
请高手解决一下 谢谢谢谢
------解决方案--------------------
if(@begintime<>'' and @endtime<>'')
begin
set @[email protected]+'and CreateDate between [email protected] +''''+ ' and '''+ @endtime +''''
end
你这样写的话，最好把参数那里，日期的类型也改成varchar
------解决方案--------------------
一般我是把数据库的日期转换成nvarchar进行判断，不然容易出现日期字段包含时间信息造成判断的时候少了一个小时的问题。