方一:
以下做法保证数据里面没有NULL值
and create_time >= isnull(@create_time,''2010-01-01'')
and create_time <= isnull(@Endcreate_time,''2200-01-01'')
and more_dat01 >= isnull(@more_dat01,''2010-01-01'')
and more_dat01 >= isnull(@Endmore_dat01,''2200-01-01'')
exec sp_executesql N'select ide_code as ''系统编号'',name as ''申请专利调查名称'',more_str01 as ''项目编号'',org_project as ''应用项目'',button_name2 as ''应用情况'',node_status as ''调查进度''
,CONVERT(char(10),create_time,102)as ''申请日期'',CONVERT(char(10),more_dat01,102)as ''希望完成日期'',CreateUser AS ''申请人'',dept_name AS ''所属部门'', OperatorName as ''调查人''
,CONVERT(char(10),end_time,102) as ''归档完成日期''
,more_txt02 AS ''调查结果''
from v_HandleZLSTResult
where ide_code like ''%''+@ide_code+''%''
and name like ''%''+@name+''%''
and more_str01 like ''%''+@more_str01+''%''
and org_project like ''%''+@org_project+''%''
and button_name2 like ''%''+@button_name2+''%''
and node_status like ''%''+@node_status+''%'' and CreateUser like ''%''+@CreateUser+''%''
and dept_name like ''%''+@dept_name+''%''and OperatorName like ''%''+@OperatorName+''%''
and create_time >= isnull(@create_time,''2010-01-01'')
and create_time <= isnull(@Endcreate_time,''2200-01-01'')
and more_dat01 >= isnull(@more_dat01,''2010-01-01'')
and more_dat01 >= isnull(@Endmore_dat01,''2200-01-01'') order by create_time desc',N'@ide_code nvarchar(4000),@name nvarchar(4000),@more_str01 nvarchar(4000),@org_project nvarchar(4000),@button_name2 nvarchar(4000),@node_status nvarchar(4000),@CreateUser nvarchar(4000),@dept_name nvarchar(4000),@OperatorName nvarchar(4000)
,@create_time nvarchar(4000),@more_dat01 nvarchar(4000),@Endcreate_time nvarchar(4000),@Endmore_dat01 nvarchar(4000)'
,@ide_code=N'',@name=N'',@more_str01=N'',@org_project=N'',@button_name2=N'',@node_status=N'',@CreateUser=N'',@dept_name=N'',@OperatorName=N''
,@create_time=NULL,@more_dat01=NULL,@Endcreate_time=NULL,@Endmore_dat01=NULL
RDLC报表:
方二:表中有40条数据,其中日期为NULL值的数据占21条,declare @TAPSDate nvarchar(4000) set @TAPSDate=null
执行: where TAPSDate>=CONVERT(datetime,'1900-01-01 00:00:00',20)
或者 where TAPSDate >= isnull(@TAPSDate,'1900-01-01')查询数据时,查询出来的数据都是只有19条,
条件:当将表中日期为NULL值的数据更新为1990-00-00 00:00:00.000后,
执行语句:select convert(datetime,TAPSDate,20) as date1,convert(char(10), TAPSDate,102) as date2 from tb where TAPSDate>=CONVERT(datetime,'1900-01-01 00:00:00',20) 或 where TAPSDate >= isnull(@TAPSDate,'1900-01-01') 时查询出来的数据是40条,