SQL DateTime format error

Question

string strQuery = "INSERT INTO [Order] (Quantity, Type, DateTime) 
values( " + qty + "," + type + "," + dtstmp.ToString("yyyy-mm-dd hh:mm:ss.fff") + ")";

SQL Query:

INSERT INTO [Order] (Quantity, Type, DateTime) values( 1,'q',2016-44-08 12:44:39.128)

Incorrect syntax error near '12'

Exception thrown: 'System.Data.Odbc.OdbcException' in System.Data.dll

Can someone help me figure out this syntax error?

Date should be within `'` and `'`, also you have to care about injection as well — sujith karivelil, Dec 08 '16 at 06:02
Use MM in "yyyy-MM-dd". You are use 'mm' and it gives you an output 44 as month. Change it to capital MM then it will give you corresponding month. — Anoopkumar, Dec 08 '16 at 06:14
You should always use [parameterized queries](http://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/). This kind of string concatenations are open for [SQL Injection](http://en.wikipedia.org/wiki/SQL_injection) attacks. And do **not** store your `DateTime` values with their string representations. Pass your `dtstmp` directly. Read [Bad habits to kick : choosing the wrong data type](https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type) — Soner Gönül, Dec 08 '16 at 06:25

score 0 · Answer 1 · answered Dec 08 '16 at 06:03

0

The datetime values should be withing single quotes. Try this

string strQuery = "INSERT INTO [Order] (Quantity, Type, DateTime) 
values( " + qty + "," + type + ",'" + dtstmp.ToString("yyyy-mm-dd hh:mm:ss.fff") + "')";

answered Dec 08 '16 at 06:03

Madhivanan

13,470
1
24
29

sujith karivelil · Answer 2 · 2016-12-08T06:11:34.653

Actually a simple fix by adding a ' before and after your dateString will not solves your issues, since your query opens a wide door for hackers. I prefer you to use parameterized queries instead for this plain-texts/concatenated strings. obviously that will fix this issue as well; the code for this will be like this:

string strQuery = "INSERT INTO [Order] (Quantity, Type, DateTime)Values( @qty,@type,@date)";
// create and open connection here 
using (SqlCommand cmdSQL = new SqlCommand(strQuery))
{
    // assign connection for this comnmand
    cmdSQL.Parameters.Add("@qty", SqlDbType.Int).Value = qty;
    cmdSQL.Parameters.Add("@type", SqlDbType.VarChar).Value = type;
    cmdSQL.Parameters.Add("@date", SqlDbType.DateTime).Value = dtstmp;
    cmdSQL.ExecuteNonQuery();
}

SQL DateTime format error

2 Answers2