Store date and time in sql server 2012 using c#

Question

i want to store date and time in SQL Server 2012 using asp.net but generate some error "Conversion failed when converting date and/or time from character string."

protected void btn Submit_Click(object sender, EventArgs e)
    {
        lbldate.Text = Convert.ToDateTime(this.txtdate.Text).ToString("dd/MM/yyyy");
        lbltime.Text = Convert.ToDateTime(this.txttime.Text).ToLongTimeString();

        TimeSpan time = new TimeSpan();
        time.ToString();
        SqlConnection con = new SqlConnection(@"Data Source=DESKTOP-O6SE533;Initial Catalog=Datertime;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False");
        SqlCommand cmd = new SqlCommand("insert date,time into DateTimedemo values('" +txtdate.Text + "','"+txttime.Text+"')", con);
        con.Open();
        int r = cmd.ExecuteNonQuery();
        if (r > 0)
        {
            Response.Write("success");

        }
        else
        {
            Response.Write("failed");
        }
}

Possible duplicate of [Conversion failed when converting date and/or time from character string while inserting datetime](https://stackoverflow.com/questions/14119133/conversion-failed-when-converting-date-and-or-time-from-character-string-while-i) — itzmebibin, Apr 11 '18 at 06:26

score 3 · Answer 1 · answered Apr 11 '18 at 06:21

Use parameterized SQL instead of building the SQL dynamically. This avoids SQL injection attacks and string formatting differences, as well as making the code clearer.

Additionally, I believe both "date" and "time" are keywords in T-SQL, so you should put them in square brackets when using them as field names.

You should attempt to perform as few string conversions as possible. Without knowing exactly what your web page looks like it's hard to know exactly how you want to parse the text, but assuming that Convert.ToDateTime is working for you (sounds worryingly culture-dependent to me) you'd have code like this:

protected void btn Submit_Click(object sender, EventArgs e)
{
    // TODO: Ideally use a date/time picker etc.
    DateTime date = Convert.ToDateTime(txtdate.Text);
    DateTime time = Convert.ToDateTime(txttime.Text);

    // You'd probably want to get the connection string dynamically, or at least have
    // it in a shared constant somewhere.
    using (var con = new SqlConnection(connectionString))
    {
        string sql = "insert [date], [time] into DateTimeDemo values (@date, @time)";
        using (var cmd = new SqlCommand(sql))
        {
            cmd.Parameters.Add("@date", SqlDbType.Date).Value = date;
            cmd.Parameters.Add("@time", SqlDbType.Time).Value = time.TimeOfDay;
            int rows = cmd.ExecuteNonQuery();
            string message = rows > 0 ? "success" : "failed";
            Response.Write(message);
        }
    }
}

I've guessed at what SQL types you're using. If these are meant to represent a combined date and time, you should at least consider using a single field of type DateTime2 instead of separate fields.

Store date and time in sql server 2012 using c#

1 Answers1