msxiyev msxiyev - 4 months ago 218x
SQL Question

Conversion failed when converting date and/or time from character string SQL in c#

I get this error when I compare to dates.

sql query command :

Select * from processTBL WHERE is=2016144 and date between '10/06/2016' and '15/06/2016'

that command work but when Fill Data to
I get converting error.

That's my c# method;

public DataGridView hesapOzeti(string command)
DataGridView gdview = new DataGridView();

if (connection.State == ConnectionState.Closed)
SqlCommand komut = new SqlCommand(command, connection);
SqlDataAdapter da = new SqlDataAdapter(komut);
DataTable dt = new DataTable();
gdview.DataSource = dt;
return gdview;

The Error:

enter image description here


A quick fix would be to send dates in an unambiguous format, so that your format is properly interpreted:

Select * from processTBL WHERE is=2016144 and date between '20160601' and '20160616'

The error comes from the fact that 15 is considered a month and thus the date is unparsable.

The correct way of doing it is to use a parameterized query:

command.Parameters.AddWithValue("@is", 2016144);
command.Parameters.AddWithValue("@FromDate", new DateTime(2016, 06, 10));    
command.Parameters.AddWithValue("@ToDate", new DateTime(2016, 06, 15));  

Your query becomes:

Select * from processTBL WHERE is = @is and date between @FromDate and @ToDate

Generally speaking, you should always try to use parameterized queries to avoid such errors and protect against SQL injection.