C# – Unable to convert textbox(dd/MM/yyyy) date to datetime format

c++datetime

I have a database date "2014-11-26". I have a calender with format(dd-MM-yyyy) I am trying to bring some values to my form from databse by textbox selected date

  protected void txtdate_TextChanged(object sender, EventArgs e)
    {

        //DateTime timeIn = Convert.ToDateTime(txtdate.Text);
       // DateTime time1 = DateTime.ParseExact(txtdate.Text, "yyyy-MM-dd", CultureInfo.InvariantCulture);
        str = "select TimeIn,TimeOut from MusterRoll where EmpCode='" + ddcode.SelectedItem.Text + "' and Date='"+time1+"'";
        dr = conn.query(str);
        if (dr.Read())
        {
            DateTime time = dr.GetDateTime(0);
            TimeSelector1.SetTime(time.Hour, time.Minute, TimeSelector1.AmPm);
            DateTime time2 = dr.GetDateTime(1);
            TimeSelector2.SetTime(time2.Hour, time2.Minute, TimeSelector2.AmPm);
        }

    }

The problem is databse date format and my calender format is different. I tried two methods(which I placed in command line)but shows error message like "input string was not in correct format". I surfed internet and find these same answers. May I know why it shows error?? i am trying to make database dateformat and calender format as same

Best Solution

First of all, a DateTime doesn't have any implicit format. It has just date and time values. String representations of them can have a format.

I strongly suspect you save your DateTime values with their string representations which is a horrible idea. Read: Bad habits to kick : choosing the wrong data type Pass your DateTime values directly to your parameterized queries instead of their string representations. Anyway..

For;

DateTime timeIn = Convert.ToDateTime(txtdate.Text); 

Convert.ToDateTime(string) method uses DateTime.Parse method with your CurrentCulture settings. That means if your string isn't a standard date and time format of your CurrentCulture your code throws FormatException. I guess dd-MM-yyyy is not a standard date and time format of your CurrentCulture.

For;

DateTime time1 = DateTime.ParseExact(txtdate.Text, "yyyy-MM-dd", CultureInfo.InvariantCulture);

When you use DateTime.ParseExact, your string and format should match exactly.

Converts the specified string representation of a date and time to its DateTime equivalent. The format of the string representation must match a specified format exactly or an exception is thrown.

In your case; they are not ("26-11-2014" and "yyyy-MM-dd"). Use dd-MM-yyyy format instead.

DateTime time1 = DateTime.ParseExact(txtdate.Text,
                                     "dd-MM-yyyy",
                                     CultureInfo.InvariantCulture);

Then you can generate the format from your time1 like;

time1.ToString("yyyy-MM-dd"); // A string formatted as 2014-11-26

For your command part, you should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks.