在两个日期之间搜索查询不接受空值到日期时间

本文介绍了在两个日期之间搜索查询不接受空值到日期时间的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 你好。当我在两个日期之间搜索记录时，它确实成功但你必须输入日期从第一天开始进行搜索 我将从这个例子中显示我需要的东西 我需要动态搜索4个文本框 1-datefrom 2-dateto 3-EmployeeNo 4-EmployeeName 但我需要的搜索必须是动态的意思 如果我输入员工不仅仅给我员工找不到在数据库中 如果我输入员工姓名给我发现使用此名称的员工如 如果我输入所有4个文本框null并输入按钮搜索获取所有数据 但是当我需要通过点击搜索按钮搜索时，我在此查询中有问题 i必须首先写日期和日期然后写员工号或员工姓名如果我需要搜索 这样我需要o员工不单独搜索或员工姓名，而不使用日期和日期 如果我不使用datefrom和dateto搜索它会给我消息错误'string wasnot被认为是有效的日期时间 我的存储过程和代码如下： ALTER proc [dbo]。[CollectsearchData] @ StartDate datetime ， @ EndDate datetime ， @ EmployeeID NVARCHAR （ 50 ）， @ EmployeeName nvarchar （ 50 ） as 开始 声明 @ SQLQuery as nvarchar （ 2000 ） SET @ SQLQuery = ' SELECT * from ViewEmployeeTest Where（1 = 1）' 如果（ @ StartDate 是 不 NULL ） 设置 @ SQLQuery = @ SQLQuery + ' 和（joindate> =''' + Cast（ @ StartDate as varchar （ 100 ））+ ' '''' 如果（ @ EndDate 不 NULL ） 设置 @ SQLQuery = @ SQLQuery + ' 和（joindate< =''' + Cast（ @ EndDate as varchar （ 100 ））+ ' '''' 如果 @ EmployeeID <> ' ' 设置 @ SQLQuery = @ SQLQuery + ' 和（EmployeeID =' + @ EmployeeID + ' ）' 如果 @ EmployeeName 不 Null 设置 @ SQLQuery = @ SQLQuery + ' AND（DriverName LIKE''％' + @ EmployeeName + ' ％'''''/ span> 打印 @sqlQuery Exec （ @ SQLQuery ） End 使用 public的功能 DataTable SearchDataA（ string ConnectionString， string EmployeeNo， string EmployeeName，DateTime StartDate，DateTime EndDate） { SqlConnection con = new SqlConnection（ConnectionString）; SqlCommand cmd = new SqlCommand（）; cmd.Connection = con; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = CollectsearchData; // work cmd.Parameters.Add（ @ StartDate，SqlDbType.DateTime）; cmd.Parameters.Add（ @ EndDate，SqlDbType.DateTime）; cmd.Parameters.Add（ @ EmployeeID，SqlDbType.NVarChar， 50 ）; cmd.Parameters.Add（ @ EmployeeName，SqlDbType.NVarChar， 50 ）; cmd.Parameters [ @ StartDate]。Value = StartDate; cmd.Parameters [ @ EndDate]。Value = EndDate; cmd.Parameters [ @ EmployeeID]。Value = EmployeeNo; cmd.Parameters [ @ EmployeeName]。Value = EmployeeName; SqlDataAdapter da = new SqlDataAdapter（）; da.SelectCommand = cmd; DataSet ds = new DataSet（）; da.Fill（ds）; DataTable dt = ds.Tables [ 0 ]; return dt; } 界面按钮搜索 尝试 { CultureInfo ukCulture = new CultureInfo（ en-GB）; FleetManagment.Fleet fleet = new FleetManagment.Fleet（）; DataTable Table = fleet.SearchDataA（ Data Source = + value1 + ;初始目录= + value2 + ;用户ID = + value3 + ;密码= + value4 + ，textBox3.Text，textBox4.Text，DateTime.Parse（ textBox1.Text，ukCulture.DateTimeFormat），Convert.ToDateTime（textBox2.Text，ukCulture.DateTimeFormat））; dataGridView1.DataSource = Table; dataGridView1.Refresh（）; } catch （例外情况） { MessageBox.Show（ex + error）; } 解决方案 如果其中一个日期为空，您需要做的就是选择一个值。例如，你可以做 COALESCE （ @ EndDate ，GetDate（）） 如果@EndDate为空，将使用今天的日期。 将你的存储过程改为这样的东西，它可能有效 ALTER proc [dbo]。[CollectsearchData] @ StartDate datetime ， @ EndDate datetime ， @ EmployeeID NVARCHAR （ 50 ） ， @ EmployeeName nvarchar （ 50 ） as 开始 声明 @ SQLQuery as nvarchar （ 2000 ） SET @ SQLQuery = ' SELECT * from ViewEmployeeTest Where（1 = 1）' Set @ SQLQuery = @ SQLQuery + ' 和（' + @ EmployeeID + ' IS NULL OR EmployeeID =' + @ EmployeeID + ' ）' 设置 @SQLQuery = @ SQLQuery + ' 和（' + @ EmployeeName + ' IS NULL或DriverName LIKE''％' + @ EmployeeName + ' ％'''' 设置 @ SQLQuery = @ SQLQuery + ' 和（' + @ StartDate + ' IS NULL或joindate> =' + @ StartDate + ' ）' 设置 @ SQLQuery = @ SQLQuery + ' 和（' + @ EndDate + ' IS NULL或joindate< =' + @ EndDate + ' ）' 打印 @sqlQuery Exec （ @ SQLQuery ） 结束 ; - ） 当向数据库发送参数时，确保它们是 DBNull.Value 当它们是 NULL 。否则参数不会发送到数据库。 或 将您的Sp parms更改为： ALTER proc [dbo]。[CollectsearchData] @StartDate datetime = null ， @EndDate datetime = null ， @ EmployeeID NVARCHAR （ 50 ）， @ EmployeeName nvarchar （ 50 ） 在这种情况下，您不必将日期参数发送到sp Hi guys. When I search record between two dates it works ok success but you must enter date from and dateto first to to make searchI will show what i need from this exampleI need to search dynamic by 4 textbox 1-datefrom2-dateto3-EmployeeNo4-EmployeeNamebut search i need must be dynamic meaningif i enter employee no only give me employee no found in databaseif i enter employee name give me employees found with this name using likeif i enter all 4 text box null and enter button search get all databut i have proplem in this query when i need to search by click search button i must write date from and date to firstly then write employee no or employee name if i need to searchso that i need to search by employee no alone or employee name alone without using date from and date to And if i search without using datefrom and dateto it give me message error 'string wasnot recognized as valid datetime"my stored procedure and code as following :ALTER proc [dbo].[CollectsearchData]@StartDate datetime, @EndDate datetime,@EmployeeID NVARCHAR(50),@EmployeeName nvarchar(50)asBeginDeclare @SQLQuery as nvarchar(2000)SET @SQLQuery ='SELECT * from ViewEmployeeTest Where (1=1)' If (@StartDate is not NULL) Set @SQLQuery = @SQLQuery + ' And (joindate >= '''+ Cast(@StartDate as varchar(100))+''')' If (@EndDate is not NULL) Set @SQLQuery = @SQLQuery + ' And (joindate <= '''+ Cast(@EndDate as varchar(100))+''')' If @EmployeeID <>''Set @SQLQuery = @SQLQuery + 'And (EmployeeID = '+ @EmployeeID+') 'If @EmployeeName Is Not Null Set @SQLQuery = @SQLQuery + ' AND (DriverName LIKE ''%'+@EmployeeName+'%'') 'Print @sqlQueryExec (@SQLQuery) EndFunction using public DataTable SearchDataA(string ConnectionString,string EmployeeNo,string EmployeeName, DateTime StartDate, DateTime EndDate){SqlConnection con = new SqlConnection(ConnectionString);SqlCommand cmd = new SqlCommand();cmd.Connection = con;cmd.CommandType = CommandType.StoredProcedure;cmd.CommandText = "CollectsearchData";//workcmd.Parameters.Add("@StartDate", SqlDbType.DateTime);cmd.Parameters.Add("@EndDate", SqlDbType.DateTime);cmd.Parameters.Add("@EmployeeID", SqlDbType.NVarChar, 50);cmd.Parameters.Add("@EmployeeName", SqlDbType.NVarChar, 50);cmd.Parameters["@StartDate"].Value = StartDate;cmd.Parameters["@EndDate"].Value = EndDate;cmd.Parameters["@EmployeeID"].Value = EmployeeNo;cmd.Parameters["@EmployeeName"].Value = EmployeeName;SqlDataAdapter da = new SqlDataAdapter();da.SelectCommand = cmd;DataSet ds = new DataSet();da.Fill(ds);DataTable dt = ds.Tables[0];return dt;}interface button searchtry{CultureInfo ukCulture = new CultureInfo("en-GB"); FleetManagment.Fleet fleet = new FleetManagment.Fleet();DataTable Table = fleet.SearchDataA("Data Source=" + value1 + ";Initial Catalog=" + value2 + ";User ID=" + value3 + ";Password=" + value4 + "",textBox3.Text,textBox4.Text, DateTime.Parse(textBox1.Text, ukCulture.DateTimeFormat), Convert.ToDateTime(textBox2.Text, ukCulture.DateTimeFormat));dataGridView1.DataSource = Table;dataGridView1.Refresh();}catch (Exception ex){MessageBox.Show(ex + "error");} 解决方案 All you need to do is choose a value if one of the dates is null. For example you can doCOALESCE(@EndDate, GetDate())which will use today's date if @EndDate is null.change ur stored procedure to something like this,it may workALTER proc [dbo].[CollectsearchData]@StartDate datetime, @EndDate datetime,@EmployeeID NVARCHAR(50),@EmployeeName nvarchar(50)asBeginDeclare @SQLQuery as nvarchar(2000)SET @SQLQuery ='SELECT * from ViewEmployeeTest Where (1=1)' Set @SQLQuery = @SQLQuery + 'And ('+ @EmployeeID+'IS NULL OR EmployeeID = '+ @EmployeeID+') 'Set @SQLQuery = @SQLQuery + 'And ('+ @EmployeeName+'IS NULL OR DriverName LIKE ''%'+ @EmployeeName+'%'') 'Set @SQLQuery = @SQLQuery + 'And ('+ @StartDate+'IS NULL OR joindate >= '+ @StartDate+') 'Set @SQLQuery = @SQLQuery + 'And ('+ @EndDate+'IS NULL OR joindate <= '+ @EndDate+') 'Print @sqlQueryExec (@SQLQuery) End;-)When sending parameters to a database make sure they are DBNull.Value when they are NULL. Otherwise the parameter is not send to the database.ORChange your Sp parms to:ALTER proc [dbo].[CollectsearchData]@StartDate datetime = null, @EndDate datetime = null,@EmployeeID NVARCHAR(50),@EmployeeName nvarchar(50)In that case you do not have to send the date parameters to the sp 这篇关于在两个日期之间搜索查询不接受空值到日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！