The correct way to use .Net DateTime in a parameterized SQL string

Question

There are several questions about using .Net DateTime in Sql statements, but none of them solved my problem.

I use the following code to query an Oracle database:

private DataTable QueryByIdAndDate(string id, DateTime fdate, DateTime tdate) { string query = "SELECT * FROM table WHERE ID = :id AND DATE_TIME BETWEEN :from AND :to" DbCommand cmd = db.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = query; DbParameter fromDate = CreateDateParameter(cmd, fdate); fromDate.ParameterName = "from"; DbParameter toDate = CreateDateParameter(cmd, tdate); toDate.ParameterName = "to"; DbParameter idParam = CreateStringParameter(cmd, id); idParam.ParameterName = "id"; cmd.Parameters.AddRange(new DbParameter[] { fromDate, toDate, idParam }); return db.ExecuteQuery(cmd); } private DbParameter CreateDateParameter(DbCommand cmd, DateTime date) { DbParameter param = cmd.CreateParameter(); param.DbType = DbType.DateTime; param.Direction = ParameterDirection.Input; param.Value = date; return param; } 

But this does not work correctly. When trying to code like this:

 DataTable result = QueryByIdAndDate("12345", DateTime.Now, DateTime.Now.AddDays(1)); 

It gives the following error: ORA-01847: the day of the month must be from 1 to the last day of the month.

I assume this is due to the way DateTime is formatted, but I don't know how to fix it in a reliable way.

Answer 1

(According to the comments ...)

It seems that in this case the order of the parameters matters ... even though you gave them names. I would not expect this, and this is a sign of a somewhat broken driver, but changing your code to:

 cmd.Parameters.AddRange(new DbParameter[] { idParam, fromDate, toDate }); 

must fix it. (This is not necessarily how you should build your parameters, by the way, but it is somewhat inconsequential here.)

Do not start specifying date / time values ​​as strings. It is a really bad idea to introduce more string conversions than you need.

Answer 2

Convert the DateTime value to a formatted date string:

 private DbParameter CreateDateParameter(DbCommand cmd, DateTime date) { DbParameter param = cmd.CreateParameter(); param.DbType = DbType.DateTime; param.Direction = ParameterDirection.Input; param.Value = date.ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture); return param; } 

Even if you send a string value, DbType is still set to DateTime , so the value must be converted correctly.

Answer 3

Oracle expects dates to be formatted as "DD-Mon-YYYY", so you basically need to format the date value this way and assign it a parameter value.

Edit: I noticed that recent versions of ODP seem to handle date options better. For example, this works fine when there is a restriction on the WHERE clause in the date / time column:

 DateTime dt = new DateTime(2012, 5, 21); cmd.Parameters.Add("some_date_param", dt); 

Running a query this way with the latest version of ODP works fine. But I have a lot of code in which date / time values ​​must be passed as formatted strings for Oracle to accept them.

Answer 4

For those who can skip it, see @kprobst's comment according to @JonSkeet's accepted answer:

It doesn't matter how well you format the parameter values, etc., if you try to use name binding without setting the BindByName property to Command .

(The whole Oracle API provider is a bunch of unwanted IMHO).