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

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

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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));

Answer 5

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

Answer 6

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

Answer 7

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 ?