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

Question

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 DateTabe I get converting error.

That's my c# method;

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

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

The Error:

http://stackoverflow.com/questions/21253566/conversion-failed-when-converting-date-and-or-time-from-character-string — mohan111, Jun 16 '16 at 05:45

Alexei - check Codidact · Accepted Answer · 2016-06-16T07:51:14.590

1

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.

edited Jun 16 '16 at 07:51

answered Jun 16 '16 at 05:47

Alexei - check Codidact

22,016
16
145
164

Go all the way and parameterize the `is` as well. Also, read [Can we stop using AddWithValue() already?](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) – Zohar Peled Jun 16 '16 at 05:49
**Thanks bro** it is work I change this `command.Parameters.AddWithValue("@FromDate", DateTime.Parse(fromDate))` – Mehemmed Jun 16 '16 at 06:07

score 0 · Answer 2 · answered Jun 16 '16 at 05:49

The date format for literals is dependant upon the locale (specifically the DATEFORMAT). The BOL page for datetime lists the locale and non-locale specific formats

https://msdn.microsoft.com/en-AU/library/ms187819.aspx

Ideally, you should use the ISO 8601 format - YYYY-MM-DDThh:mm:ss[.mmm] or YYYYMMDD[ hh:mm:ss[.mmm]]

In your code, try date between '20160610' and '20160615'

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

2 Answers2