在两个日期之间检索数据 [英] retrivte data between two dates

问题描述

SqlConnection con = new SqlConnection("Data Source=server;Initial Catalog=f_library;User ID=sa;Password=ahmed");
con.Open();
SqlDataAdapter dad = new SqlDataAdapter("Select * from esrdat where esrdat_date between '" + Convert.ToDateTime(dateTimePicker1.Value) + "' and '" + Convert.ToDateTime(dateTimePicker2.Value) + "'", con); 
DataSet ds = new DataSet();
dad.Fill(ds);
if (ds.Tables[0].Rows.Count > 0)
{
    esrdatDataGridView.DataSource = ds.Tables[0];
}

con.Close();

我收到此错误

I get this error

System.Data.SqlClient.SqlException was unhandled
Message="Syntax error converting datetime from character string."

推荐答案

不要这样做！

Don''t do that!

SqlDataAdapter dad = new SqlDataAdapter("Select * from esrdat where esrdat_date between '" + Convert.ToDateTime(dateTimePicker1.Value) + "' and '" + Convert.ToDateTime(dateTimePicker2.Value) + "'", con); 

它所做的就是获取一个有效的DateTime，使用默认的转换为字符串，然后尝试将其转换回DateTime.然后将其(作为数字，通过另一个默认转换转换回字符串)传递给SQL.但是以您的本地格式，而不是任何期望的SQL格式(ISO格式).

试试:

All it does is take a valid DateTime, use a default conversion to a string, then try to convert it back to a DateTime. Then pass that (as a number, converted back to a string via another default conversion) to SQL. But in your local format, rather than anythign SQL is expecting, which is ISO format.

Try:

SqlDataAdapter dad = new SqlDataAdapter("Select * from esrdat where esrdat_date between @START AND @END", con);
dad.SelectCommand.Parameters.AddWithValue("@START", dateTimePicker1.Value);
dad.SelectCommand.Parameters.AddWithValue("@END", dateTimePicker2.Value);

尝试使用DateTime.TryParse 方法 [ ^ ]到确保您正在将适当的string 转换为DateTime值.

Try using DateTime.TryParse method[^] to ensure you are converting an appropriate string to DateTime value.

这篇关于在两个日期之间检索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！