2

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();
Jad Chahine
  • 6,849
  • 8
  • 37
  • 59
  • http://stackoverflow.com/questions/12957635/sql-query-to-insert-datetime-in-sql-server – Xi Sigma Mar 21 '15 at 11:10
  • 3
    [SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection – marc_s Mar 21 '15 at 11:12
  • Also check datatype of birthdate in table – Tharif Mar 21 '15 at 11:47

5 Answers5

13

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();
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
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")+"')";
DMVC
  • 240
  • 1
  • 2
  • 15
0

Try including quotes:

cmd.CommandText="INSERT INTO person (birthdate) VALUES('"+dateTimePicker.Value.Date+"')";

I'd recommend using parameters too.

Brian Mains
  • 50,520
  • 35
  • 148
  • 257
0

Try this as string format:

cmd.CommandText="INSERT INTO person(birthdate)VALUES('"+dateTimePicker.Value.Date+"')";
Eric Aya
  • 69,473
  • 35
  • 181
  • 253
Developer
  • 1
0

dateTimePicker stores values as 1/1/1900 12:00:00 AM so you should use DATETIME if you're trying to store it since DATETIME's format is: YYYY-MM-DD HH:MI:SS.

You can print the dateTimePicker value using

MessageBox.Show(dateTimePicker.Value.ToString());

to see for yourself.

0xInfection
  • 2,676
  • 1
  • 19
  • 34
Jonathan Acevedo
  • 3
  • 1
  • 3