Querying Excel column between 2 dates using C#

Question

Hi im trying to select a data from an excel between two dates. Im a bit stuck as the data in excel is just text type. How would i query this?

string Date = DateTime.Now.AddDays(7).ToString("dd/MM/yyyy HH:mm");

string Date2 = DateTime.Now.ToString("dd/MM/yyyy HH:mm");

MyCommand = new System.Data.OleDb.OleDbDataAdapter("select [Req Start Date] from [CR$]", BETWEEN Date and Date2 ? MyConnection);

Answer 1

You can query between two dates by using standard SQL syntax, but you need to assign the dates as date parameters to the OleDbDataAdapter. Otherwise it will treat the dates as text and you will not get proper matches.

DataSet ds = new DataSet();

using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(SQL, conn))
{
    // Format the dates as dd/mm/yyyy

    string date1 = DateTime.Now.AddDays(7).ToString("dd/MM/yyyy");
    string date2 = DateTime.Now.ToString("dd/MM/yyyy");

    // Set up the SQL with the two date paramters

    string SQL = "SELECT [Req Start Date] FROM [CR$] WHERE [Req Start Date] BETWEEN ? AND ?";

    // Assign the two dates

    dataAdapter.SelectCommand.Parameters.Add("@p1", OleDbType.Date).Value = date1;
    dataAdapter.SelectCommand.Parameters.Add("@p2", OleDbType.Date).Value = date2;

    // Run the query

    dataAdapter.Fill(ds);
}