CODEWITHSUNDEEP
c#sql
sacpat
sacpat

Reputation: 43

insert datetime value gives error "Incorrect syntax near 12"

DateTime myDateTime =  Convert.ToDateTime(rd2[0].ToString())
values = myDateTime.ToString("yyyy-MM-dd HH:mm:ss") + " , " + rd2[1].ToString()+ " , " + rd2[2].ToString()+ " , " + rd2[3].ToString()+ " , " + rd2[4].ToString()+ " , " + rd2[5].ToString() ;

i am trying to insert date 2016-04-22 12:58:11 in sql server table of datatype datetime but it gives error "Incorrect syntax near 12"

Upvotes: 2

Views: 14939

Answers (2)

CathalMF
CathalMF

Reputation: 10055

The string you end up with is similar to this:

2016-04-22 00:00:00,2016-04-22 00:00:00,2016-04-22 00:00:00,2016-04-22 00:00:00

Inserting that into a SQL statement is invalid. You need to wrap each date in single quotes so that you have:

'2016-04-22 00:00:00','2016-04-22 00:00:00','2016-04-22 00:00:00','2016-04-22 00:00:00'

Either way this makes your life difficult and makes your code subject to sql injection and insecure. Consider using parameters like this.

string exampleSQL = "SELECT * from mydatetable where dateOne = @date1 and dateTwo = @date2";
SqlConnection connection = new SqlConnection(/* connection info */);
SqlCommand command = new SqlCommand(sql, connection);

command.Parameters.Add("@date1", SqlDbType.DateTime).Value = myDateTime;
command.Parameters.Add("@date2", SqlDbType.DateTime).Value = rd2[1];

This way you dont need to worry about formatting. The system automatically will replace the @date1 and @date2 with the values you specified and it will deal with adding the nescessary structure of the SQL without you having to worry about it.

Upvotes: 13

oagostinho
oagostinho

Reputation: 64

I strongly suggest using "parametrizing your sql queries"...For example, you can check it out here: http://www.dreamincode.net/forums/topic/268104-the-right-way-to-query-a-database-parameterizing-your-sql-queries/

Cheers!

Upvotes: 0

Related Questions