How to get records between two dates c# ms-sql

Ashish Khadye

Hello,

I have developed one windows application, and I want to search records between two dates selected by user.

Please tell me exact query to select records between two dates

Datatype of Date in sql table is nvarchar(50)

My code is :

dataGridViewSearchResults.Visible = true;

labeltot.Visible = true;

string startdate;

string enddate;

startdate = dateTimePickerstartdate.Value.ToString("dd/MM/yyyy");

enddate = dateTimePickerenddate.Value.ToString("dd/MM/yyyy");

double i = 0;

con.Close();

SqlCommand cmd = new SqlCommand("select * from Expenses where Date>='" + startdate.ToString() + "' and Date<='" + enddate.ToString() + "'", con);

con.Open();

cmd.ExecuteNonQuery();

DataTable dt = new DataTable();

SqlDataAdapter da = new SqlDataAdapter(cmd);

da.Fill(dt);

dataGridViewSearchResults.DataSource = dt;

foreach (DataRow dr in dt.Rows)

{

i = i + Convert.ToDouble(dr["Amount"].ToString());

}

lbltotal.Text = i.ToString();

i have also tried this query -

select * from Expenses where Date BETWEEN '" + startdate.ToString() + "' AND '" + enddate.ToString() + "'

But its not giving proper result.

e.g. If we gave fromdate as - 01/07/2018 and todate as 31/07/2018 then in the results it gives records of 01/08/2018 also...

How to get desired result?

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ashish Khadye8y ago
Hello All,

Changing the data type from nvarchar to datetime solved the issue.

Thanks
Jignesh Kumar8y ago
Hi Ashish,

Is your issue resolved ?
Jenith Thakkar8y ago
Hi Not Sure but might be there's a formation issue

startdate = dateTimePickerstartdate.Value.ToString("yyyy/MM/dd");
enddate = dateTimePickerenddate.Value.ToString("yyyy/MM/dd");
double i = 0;
con.Close();
SqlCommand cmd = new SqlCommand("select * from Expenses where Date>='" + startdate + "' and Date<='" + enddate + "'", con);
con.Open();
cmd.ExecuteNonQuery();

For More info please refer this
https://stackoverflow.com/questions/5125076/sql-query-to-select-dates-between-two-dates
Amit Gupta8y ago
Just change the below snippet

startdate = dateTimePickerstartdate.Value.ToString("yyyy/MM/dd");
enddate = dateTimePickerenddate.Value.ToString("dyyyy/MM/dd");
Jignesh Kumar8y ago
Hi,

While passing in query you have to parse the dates

select * from Expenses where Date BETWEEN '" + ParseDate(startdate)+ "' AND '" + ParseDate(enddate)+ "'