How to INSERT date into SQL database date column using dateTimePicker?

79,535

Solution 1

What you really should do is use parameters to avoid SQL injection attacks - and it also frees you from string formatting dates - also a good thing!

//cmd is sql command
cmd.CommandText = "INSERT INTO dbo.Person(birthdate) VALUES(@Birthdate);";

cmd.Parameters.Add("@Birthdate", SqlDbType.Date).Value = dateTimePicker.Value.Date;

//con is sql connection
con.Open();
cmd.ExecuteNonQuery();
con.Close();

Also, it's a recommend best practice to put your SqlConnection, SqlCommand and SqlDataReader into using(....) { .... } blocks to ensure proper disposal:

string connectionString = ".......";
string query = "INSERT INTO dbo.Person(birthdate) VALUES(@Birthdate);";

using (SqlConnection con = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand(query, conn))
{
     cmd.Parameters.Add("@Birthdate", SqlDbType.Date).Value = dateTimePicker.Value.Date;

     con.Open();
     cmd.ExecuteNonQuery();
     con.Close();
} 

Solution 2

As mentioned before the best practice is to use parameters, but if you really need to use a TSQL statement from source you should use date in the format: yyyymmdd

cmd.CommandText="INSERT INTO person (birthdate) VALUES('"+dateTimePicker.Value.Date.ToString("yyyyMMdd")+"')";
Share:
79,535
Jad Chahine
Author by

Jad Chahine

Contact me on [email protected] LinkedIn profile

Updated on July 09, 2022

Comments

  • Jad Chahine
    Jad Chahine over 1 year

    I have a birthdate column of type Date in sql database

    And in my application I use a dateTimePicker to get the birth date

    But when i am trying to insert the date taken from the dateTimePicker:

    I get an error :

    Incorrect syntax near '12'

    And when I try to debug the code I find that the value taken from the dateTimePicker is

    Date = {3/21/2015 12:00:00 AM}

    The CODE:

    //cmd is sql command
    cmd.CommandText="INSERT INTO person (birthdate) VALUES("+dateTimePicker.Value.Date+")";
    //con is sql connection
    con.Open();
    cmd.ExecuteNonQuery();
    con.Close();
    
  • Hemal
    Hemal about 6 years
    You forgot a space before VALUES
  • 0xInfection
    0xInfection about 5 years
    Add more info on this.