locked
Error - Conversion failed when converting date and/or time from character string. RRS feed

  • Question

  • User-1499457942 posted

    Hi

      I have below code

    SqlCommand cmd = new SqlCommand("INSERT INTO [Period] (Date1,Date2) VALUES('" + Convert.ToDateTime(txt_Date1.Text) + "','" + Convert.ToDateTime(txt_Date2.Text) + "')", con);

    Thanks

    Tuesday, November 13, 2018 2:26 PM

All replies

  • User475983607 posted

      I have below code

    SqlCommand cmd = new SqlCommand("INSERT INTO [Period] (Date1,Date2) VALUES('" + Convert.ToDateTime(txt_Date1.Text) + "','" + Convert.ToDateTime(txt_Date2.Text) + "')", con);

    Clearly you have not taken a few seconds to view the resulting SQL string in the debugger...  

    Build a parameter query like you've done in many past posts.

    Tuesday, November 13, 2018 2:32 PM
  • User-1499457942 posted

    Hi

      Trying like this not working

    SqlCommand cmd = new SqlCommand("INSERT INTO [Period] (Date1,Date2) VALUES('" + @Date11 + "','" + @Date12 + "')", con);
    cmd.Parameters.AddWithValue("@Date11", txt_Date1.Text.Trim());
    cmd.Parameters.AddWithValue("@Date12", txt_Date1.Text.Trim());

    Thanks

    Tuesday, November 13, 2018 3:01 PM
  • User475983607 posted

    Hi

      Trying like this not working

    SqlCommand cmd = new SqlCommand("INSERT INTO [Period] (Date1,Date2) VALUES('" + @Date11 + "','" + @Date12 + "')", con);
    cmd.Parameters.AddWithValue("@Date11", txt_Date1.Text.Trim());
    cmd.Parameters.AddWithValue("@Date12", txt_Date1.Text.Trim());

    Thanks

    There are SQL syntax errors.  Again basic troubleshooting or simple reading reference docs should highlight this rudimentary syntax error.

    SqlCommand cmd = new SqlCommand("INSERT INTO [Period] (Date1, Date2) VALUES(@Date11, @Date12)", con);

    The following code has a bug too as you've used the same txt_Date1 control.

    cmd.Parameters.AddWithValue("@Date11", txt_Date1.Text.Trim());
    cmd.Parameters.AddWithValue("@Date12", txt_Date1.Text.Trim());

    Consider adding basic input validation which has been suggested many times over many of your previous threads. 

    Lastly, start using the visual Studio debugger please.

    Tuesday, November 13, 2018 3:12 PM
  • User-1716253493 posted

    Hi JagjitSingh, both ways are confused,

    Textbox text is a string, sqlquery is a string, sql parameters are dates

    I think, if you want use textbox text in the query you dont need to convert it to datetime

    SqlCommand cmd = new SqlCommand("INSERT INTO [Period] (Date1,Date2) VALUES('" + txt_Date1.Text + "','" + txt_Date2.Text + "')", con);

    The textbox text must be contain correct sql date format

    But, if you want use parameterize query, you need to convert it as datetime in C#, it's depend your appication date format

    You can use many c# date function to convert it

    SqlCommand cmd = new SqlCommand("INSERT INTO [Period] (Date1,Date2) VALUES(@Date11,@Date2)", con);
    cmd.Parameters.AddWithValue("@Date11", Convert.ToDateTime(txt_Date1.Text));
    cmd.Parameters.AddWithValue("@Date12", Convert.ToDateTime(txt_Date1.Text));

    Wednesday, November 14, 2018 12:47 AM
  • User-893317190 posted

    Hi JagjitSingh,

    Because your field in sqlserver is of type datetime, ado.net will try to convert your parameter to datetime .

    If it could not convert your data to datetime , it will cause error.

    But string of your textbox may have an incorrect format, I suggest you could use DateTime.ParseExactto convert your string in a specified format.

    https://docs.microsoft.com/en-us/dotnet/api/system.datetime.parseexact?view=netframework-4.7.2

    Below is my code."yyyy-MM-dd" represents string like "1999-09-21", you could also specify other format, please refer to https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings

    And you could use parameterized placeholder like  @date12 to represent your parameter, so that you could write your sql in the same way regardless of the type of your parameter.

    using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand com = new SqlCommand("INSERT INTO [Period] (Date1, Date2) VALUES(@Date11, @Date12)", con))
                    {
                        com.Parameters.AddWithValue("Date11", DateTime.ParseExact(TextBox1.Text, "yyyy-MM-dd", CultureInfo.InvariantCulture));
                        com.Parameters.AddWithValue("Date12", DateTime.ParseExact(TextBox2.Text, "yyyy-MM-dd", CultureInfo.InvariantCulture));
                        con.Open();
                        com.ExecuteNonQuery();
                    }
                }

    Best regards,

    Ackerly Xu

    Wednesday, November 14, 2018 5:33 AM
  • User-1499457942 posted

    Hi

      I have below code but it is giving error 'String was not recognized as a valid datetime" . In Database i have Date DataType

    SqlCommand cmd = new SqlCommand("INSERT INTO [Period] (StDate,ClgDate) VALUES(@StDate,@ClgDate)", con);
    cmd.Parameters.AddWithValue("@StDate", DateTime.ParseExact(txt_StartDate.Text, "yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture));
    cmd.Parameters.AddWithValue("@ClgDate", DateTime.ParseExact(txt_ClosingDate.Text, "yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture));
    con.Open();

    Thanks

    Wednesday, November 14, 2018 5:08 PM
  • User753101303 posted

    It means the string to date(time) conversion fails.

    Which values do you have in txt_StartDate.Text or txt_ClosingDate.Text ? Users are supposed to use to use which convention for entering dates ?

    Wednesday, November 14, 2018 5:12 PM