String Conversion problem in executing query

Question

string strConnection = @"server=.\SQLEXPRESS;Database=Northwind ; Integrated Security=SSPI";
string strCommand ="Select * from Orders where OrderDate='"+7/4/1996+"'";
sqlDataAdapter dataAdapter = new SqlDataAdapter(strCommand, strConnection);
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet, "Orders");
dataGridView1.DataSource = dataSet.Tables["Orders"].DefaultView;
I want to populate the grid depending upon the date of Orders but my query is not interpreted as select * from Orders where orderDate='7/4/2001';

Kumar AG · Answer

My dear friend the date format in SLQ Server is "yyyy-MM-dd HH:mm:ss.MMM" And SQL server will accept the date in any valid date format. Default will be local settings whether it is "MM-dd-yyyy" or "dd-MM-yyyy" etc. Here in the above question he has problem passing values. So the best way to pass the values is by using parameters. Hope this helps.

Roei Bar · Answer

well my friend, the issue is that SQL doesnt like dates in any manner, go to SQL type the following select getdate() you will see the date format by month day year hours min, seconds and so on. just pass in the date as it requests and it will work perfectly fine

Kumar AG · Answer

Use command parameters Here is sample code using ( SqlConnection sqlconn = new SqlConnection ( "your Connection string" )) { sqlconn.Open(); using ( SqlCommand cm= sqlconn.CreateCommand()) { cm.CommandType = CommandType .Text; cm.CommandText = "SELECT * FROM orders where orderdate >= @orderdate" ; cm.Parameters.AddWithValue( "@orderdate" , "07-04-1996" ); SqlDataAdapter dataAdapter = new SqlDataAdapter (cm); dataAdapter.Fill(ds, "orders" ); } }