Click here to Skip to main content
15,671,149 members
Please Sign up or sign in to vote.
5.00/5 (2 votes)
Hello All,

My problem is simple I have a birthdate column of type date in my sql database and in my application I use dateTimePicker to get the birthdate, but when trying to insert the date I got from the dateTimePicker I get an error Incorrect syntax near '12'

And when I try to debug the code I find that the value I get from the dateTimePicker is Date = {7/16/2011 12:00:00 AM}

the CODE:
//cmd is sql command
cmd.CommandText="INSERT INTO person (birthdate) VALUES("+dateTimePicker.Value.Date+")";
//con is sql connection

Thanks in advance
Updated 25-Mar-20 5:53am

you should add your date using a parameter. you snipet from above should look like below

cmd.CommandText = "INSERT INTO person (birthdate) VALUES(@date)";
            cmd.Parameters.Add(new SqlParameter("@date", dateTimePicker.Value.Date));
            //con is sql connection

Help With Parameters[^]
Share this answer
As the datatype for the date you used is only Date and your datepicker value is returning Time along with date.
Either change the datatype to datetime and or pass only Date value to insert.
Share this answer
a1mimo 16-Jul-11 6:39am    
Thank You mkgoud
I just solved the problem right now and to be fair I thought it would be as you said too but it was because I forgot to put the value of the date between '' like this cmd.CommandText="INSERT INTO person (birthdate) VALUES('"+dateTimePicker.Value.Date+"')";
but still there is something I wish to know how to get only the date value from the dateTimePicker if dateTimePicker.Value.Date dont get only the date?
m@dhu 16-Jul-11 6:53am    
As you said in the question it is returning '7/16/2011 12:00:00 AM'. so I came to conclusion it returns time also.
a1mimo 16-Jul-11 10:11am    
yes I know and btw my vote of 4
I way to solve the problem would be to change the data type in the SQL DB to VARCHAR(50)

and the execute the following SQL query in your code.


cmd.CommandText = "INSERT INTO dbo.Person (birthdate) VALUES ('" + this.dateTimePicker.Text + "')";
cmd.Connection = con;

This will display the value as 16 July 2011 in your SQL DB without the time value. This can then be converted to to a short date string be using the following
string dateString = this.dateTimePicker1.Text;
DateTime Date = Convert.ToDateTime(dateString.ToString());

You can then output the date to a short date format using the following;


Hope this helps.

Use Sql parameters to avoid SQL injection[^].

Enter Null Values for DateTime Column of SQL Server[^](Explains how to pass values using sql parameters.)
Share this answer
a1mimo 16-Jul-11 10:11am    
Thank you Danny
I already discovered the problem it was the single quote I didnt put it around the date value but your solutions will work as well so my vote of 4
Use string("dd/MM/yyyy")
In your expression.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900