MSSQL 日期查询包含NULL值

方一：

以下做法保证数据里面没有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条，

MSSQL 日期查询 包含NULL值

MSSQL 日期查询包含NULL值