inserting a string that looks like a date to a database date column

Question

im having a problam inserting a string that looks like a date (23.02.2015) from datagridview to my local database date column that .

i know i need to transfer my string "23.02.2015" to 23/02/2015 and convert it to a date variable before im inserting it to my database date column but i dont know how to do it inside my code :

private void button3_Click(object sender, EventArgs e)
    {

            foreach (DataGridViewRow row in dataGridView1.Rows)
            {
                string constring = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\john\Documents\Visual Studio 2015\Projects\Project\Project\DB.mdf;Integrated Security=True";
                using (SqlConnection con = new SqlConnection(constring))
                {

                    using (SqlCommand cmd = new SqlCommand("INSERT INTO ResultsTable VALUES(@Date, @TagNumber)", con))
                    {
                        cmd.Parameters.AddWithValue("@Date", row.Cells["Exposure Date"].Value);
                        cmd.Parameters.AddWithValue("@TagNumber", row.Cells["Device #"].Value);

                        cmd.ExecuteNonQuery();     
                    }


                }
            }

        MessageBox.Show("Records inserted.");

    }

in short - im having a problam to convert a string like "23.05.2014" to a date type like 23/05/2014 to insert it to date column in my database in my code .

Do you want something like `Convert.ToDateTime(Regex.Replace(input, ".", "/"))` to convert string automatically into datetime before inserting? — Tetsuya Yamamoto, Jan 27 '17 at 06:28

score 1 · Accepted Answer · answered Jan 27 '17 at 06:31

This might do the trick for you if your string date is always in the given format. DateTime.ParseExact Converts the specified string representation of a date and time to its DateTime equivalent using the specified format and culture-specific format information. The format of the string representation must match the specified format exactly.

string smdt = row.Cells["Exposure Date"].Value;
//This is the string format which is going to parse the Date
string format = "dd.MM.yyyy";
DateTime dt = DateTime.ParseExact(smdt, format, CultureInfo.InvariantCulture, DateTimeStyles.AssumeUniversal);

and then

cmd.Parameters.AddWithValue("@Date", dt);

Happy to help @shlezz :) Happy Coding Kudos – Mohit S Jan 27 '17 at 07:08 — Mohit S, Jan 27 '17 at 07:08

score -1 · Answer 2 · answered Jan 27 '17 at 06:39

-1

Please try below code:

string stringDate = "23.02.2015";  // Hold your string date here (row.Cells["Exposure Date"].Value)
DateTime date = DateTime.ParseExact(stringDate, "dd.MM.yyyy", CultureInfo.InvariantCulture);

This will convert your string date (stringDate) to a datetime (date) object, and you can pass this date object to stored procedure parameter.

I believe, you are always getting string date in this ("dd.MM.yyyy") format. Else, you will need to change it as per your string format.

Please let me know if this helps.

answered Jan 27 '17 at 06:39

Vikas_Systematix

39
4

How is it different from my answer?? – Mohit S Jan 27 '17 at 06:40
I just saw, you posted same answer.. moreover, about 7-8 minutes before mine. So, it is simple to think.. I just modified your answer and re-posted. But believe me my friend, this was not the case.. actually when I found this question.. there was not a single answer but I took it more than 10 minutes (not sure but possibly got a Skype call that time) to post my answer and without REFRESHING the window and then I am seeing this today. Sorry. – Vikas_Systematix Jan 28 '17 at 07:08

inserting a string that looks like a date to a database date column

2 Answers2