Conversion failed when converting date and/or time from character string.?

Question

1.00/5 (2 votes)

See more:

SQL-Server-2005

, +

hi

am using two dates and one drop down to searh records and display in grid but am getting error

"

Conversion failed when converting date and/or time from character string."

in button click

protected void searchCase_Click(object sender, EventArgs e)
  {



      //end add 18-11-2013
      DataLayer dl = new DataLayer();

      string fromDate = DateTime.Now.ToShortDateString();
      string toDate = DateTime.Now.ToShortDateString();

      ////DateTime fromDate = Convert.ToDateTime(FrmDate.Text);
      ////DateTime ToDate = Convert.ToDateTime(toDate.Text);
      ////DataTable sortedDataTable = dl.DisplayFilterCaseReportAll();

      DataTable sortedDataTable = dl.DisplayFilterCaseReport(DropDownList1.SelectedItem.Text, fromDate, toDate);
      if (sortedDataTable.Rows.Count > 0)
      {
          Panel1.Visible = true;
          GridView1.DataSource = sortedDataTable;
          GridView1.DataBind();
      }
      else
      {
          GridView1.DataSource = null;
          GridView1.DataBind();
      }

  }

public DataTable DisplayFilterCaseReport(string court, string startDate, string Endate)
    {
        string dt = DateTime.Now.ToShortDateString();
        try
        {

            string qry;
            sqlconn.Open();
            if (court=="Select")
             qry = "SELECT k_tbl.Fno as [رقم الملف], k_tbl.Dno as [رقم القضية], k_tbl.HnoEng as [المحكمة], k_tbl.TknoEng as [نوع القضية], k_tbl.SubjectEng as [موضوع الدعوى],k_tbl.MnoEng as [اسم الموكل],k_tbl.MAnoEng as [صفة الموكل],k_tbl.AccusedEngName as [اسم الخصم],S_tbl.Sdate as [تاريخ الجلسة],S_tbl.Dec as [قرار],k_tbl.lawername as [اسم المحامي ],k_tbl.amount as [المبلغ] FROM k_tbl INNER JOIN S_tbl ON k_tbl.Fno = S_tbl.FileNo WHERE  (S_tbl.Sdate <=CONVERT(datetime,'" + Endate + "',103)) AND (S_tbl.Sdate >= CONVERT(datetime,'" + startDate + "',103)) ORDER BY Sdate DESC";
            else
             qry = "SELECT k_tbl.Fno as [رقم الملف], k_tbl.Dno as [رقم القضية], k_tbl.HnoEng as [المحكمة], k_tbl.TknoEng as [نوع القضية], k_tbl.SubjectEng as [موضوع الدعوى],k_tbl.MnoEng as [اسم الموكل],k_tbl.MAnoEng as [صفة الموكل],k_tbl.AccusedEngName as [اسم الخصم],S_tbl.Sdate as [تاريخ الجلسة],S_tbl.Dec as [قرار],k_tbl.lawername as [اسم المحامي ],k_tbl.amount [المبلغ] FROM k_tbl INNER JOIN S_tbl ON k_tbl.Fno = S_tbl.FileNo WHERE k_tbl.HnoEng LIKE '" + court + "' AND   (S_tbl.Sdate <=CONVERT(datetime,'" +  Convert.ToDateTime(Convert.ToDateTime((Endate)).ToString("yyyy-MM-dd hh:MM:ss")) + "',103)) AND (S_tbl.Sdate >= CONVERT(datetime,'" +  Convert.ToDateTime(Convert.ToDateTime((startDate)).ToString("yyyy-MM-dd hh:MM:ss"))+ "',103)) ORDER BY Sdate DESC";
            DataTable ds = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(qry, sqlconn);
            da.Fill(ds);

            return ds;
        }

        catch (Exception ex)// this line am getting error
        {
            return null;
        }
        finally
        {
            sqlconn.Close();
        }
    }

can any one suggest me>>

thank you

Posted 17-Nov-13 20:28pm

ythisbug

Add a Solution

Comments

Sergey Alexandrovich Kryukov 18-Nov-13 2:30am

In what line?
—SA

ythisbug 18-Nov-13 2:47am

I mentioned in last line

Sergey Alexandrovich Kryukov 18-Nov-13 3:01am

No, that's not it. This is where it is caught, not thrown.
By the way, you are doing really bad thing, at least bad at development stages: you block exception propagation in your exception handling. It's better not to catch exceptions at all. Catching should be done in very few points of code, in particular, on the top of stack of each thread, main event loop of the application, places where you can really fix the problem, and the like.
—SA

ythisbug 18-Nov-13 3:40am

ok thank you..but error is not using caching rite..now i solved this ..thank you...
in my database time format saved in "2013-11-15" and while fetching i was getting value as "15-11-2013"..so that was the issue..
thanks again

Sergey Alexandrovich Kryukov 18-Nov-13 12:13pm

Great. But pay attention: you need to run code under the debugger and look at the exception StackTrace (under the debugger or not).
Good luck.
—SA

ythisbug 18-Nov-13 12:17pm

hmmm

Sergey Alexandrovich Kryukov 18-Nov-13 12:58pm

Agree with you: hmmm. :-)
When you have a problem related to unexpected exception you get, you need to provide relevant code and (part of) StackTrace, which you would need yourself to find out where the exception was thrown and propagated. Often, knowing and showing the point of throwing is enough, but in some other cases you also need to find a root cause which does not throw exception but, say, prepares invalid object, or something.
—SA

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

tgrt · Answer 1 · 2013-11-18T15:51:00

The style parameter (3rd parameter) in your call to the T-SQL CONVERT function is invalid. The value in this case would apply if you were converting from datetime to a character based type. It should work if you remove it.

That said, you're going through a lot of work to simply strip off the time value of you to and from dates. You don't need to convert it to a string and then back to a date. The following code will strip off the time component:

C#

DateTime fromDate;
if (DateTime.TryParse(FrmDate.Text, out fromDate)) {
     fromDate = fromDate.Date;
}