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:

stackoverflow.com/questions/21253566/…

mohan111
– mohan111

2016-06-16 05:45:54 +00:00
Commented Jun 16, 2016 at 5:45 — mohan111
– mohan111, Commented Jun 16, 2016 at 5:45

Alexei - check Codidact · Accepted Answer · 2016-06-16 07:51:14Z

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, 2016 at 7:51

answered Jun 16, 2016 at 5:47

Alexei - check Codidact

23.2k22 gold badges159 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zohar Peled Over a year ago

Go all the way and parameterize the is as well. Also, read Can we stop using AddWithValue() already?

Mehemmed Over a year ago

Thanks bro it is work I change this command.Parameters.AddWithValue("@FromDate", DateTime.Parse(fromDate))

Liesel · Accepted Answer · 2016-06-16 05:49:17Z

0

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'

answered Jun 16, 2016 at 5:49

Liesel

2,9792 gold badges14 silver badges18 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related