Proper Way To Use .Net DateTime in Parameterized SQL String

Question

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

I'm using 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 it does not work properly. When trying the code out like so:

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

It gives the following error: ORA-01847: day of month must be between 1 and last day of month

I'm assuming it has to do with the way the DateTime is formatted but I don't know the proper way to fix this in a reliable way.

You wouldn't happen to be running it for the 30th of a month with only 30 days would you? — Joshua Drake
– Joshua Drake, Commented Apr 24, 2012 at 20:09
Nope. It failed with the example I showed using DateTime.Now and DateTime.Now.AddDays(1); — user12345613
– user12345613, Commented Apr 24, 2012 at 20:10
Does your driver definitely support named parameters? If it's trying to use them by position, it'll end up using the "id" value as the "to" part, which could be broken. Which driver are you using? — Jon Skeet
– Jon Skeet, Commented Apr 24, 2012 at 20:11
@JonSkeet Wow you guessed it. I'm using Oracle.DataAccess and it must be using them by position because I changed the order of the parameters as I put them into the array and now it is working. What an annoying result after an hour of messing with it. Put that as an answer and you win, thank you! — user12345613
– user12345613, Commented Apr 24, 2012 at 20:15
@user12345613: Good to see psychic debugging working every so often :) — Jon Skeet
– Jon Skeet, Commented Apr 24, 2012 at 20:19

Jon Skeet · Accepted Answer · 2012-04-24 20:18:18Z

11

(As per comments...)

It looks like in this case, the order of the parameters matters... despite the fact that you've given them names. I wouldn't have expected this, and it's the sign of a somewhat broken driver, but changing your code to:

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

should fix it. (This isn't necessarily the way you should be constructing your parameters, by the way, but that's somewhat immaterial here.)

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

answered Apr 24, 2012 at 20:18

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user12345613 Over a year ago

Yes I didn't even think about the order of that array (which was foolish) since they are named parameters. Also, I'm open for any comments or suggestions on properly constructing the parameters.

Uwe Keim Over a year ago

In my experience there are lots of things broken in oracle .net drivers, including parameter order or the need to change SQL statements in order to get rid of NULL reference exceptions.

Jon Skeet Over a year ago

@user12345613: I can't say it's an area I have much experience with, but if you use the strongly-typed command parameter collection, you're likely to get options such as AddWithValue which make life easier.

user12345613 Over a year ago

@JonSkeet I'll have to check that out. As for now, I'm glad this is working.

kprobst Over a year ago

The OracleCommand object in ODP.Net has a property called BindByName, which when set to true eliminates this problem. It is set to false by default because previous versions of the driver(s) used to bind by position.

|

mgnoonan · Accepted Answer · 2012-04-24 20:31:05Z

3

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 though you are sending a string value, the DbType is still set to DateTime, so the value should get converted properly.

edited Apr 24, 2012 at 20:31

answered Apr 24, 2012 at 20:11

mgnoonan

7,2005 gold badges26 silver badges28 bronze badges

2 Comments

Jon Skeet Over a year ago

Note that even if this were actually the problem, you'd probably want to use the invariant culture, and you'd definitely want to change "hh" to "HH". I would strongly advise against performing such conversions yourself though.

mgnoonan Over a year ago

Nothing gets by @JonSkeet. Agreed, I jumped straight to the formatting issue and never considered the parameter order. Updated per your suggestions.

kprobst · Accepted Answer · 2012-04-24 20:15:02Z

2

Oracle expects dates formatted as 'DD-Mon-YYYY', so you need to basically format your date value that way and assign it to the parameter value.

Edit: I've noticed that recent versions of ODP seem to handle date time parameters better. For example, this works just fine when there is a constraint on a WHERE clause over a 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 where date/time values need to be passed as formatted strings in order for Oracle to accept them.

answered Apr 24, 2012 at 20:15

kprobst

16.8k5 gold badges35 silver badges53 bronze badges

Comments

Gary McGill · Accepted Answer · 2018-05-08 13:25:45Z

2

For those that might miss it, see the comment by @kprobst under the accepted answer by @JonSkeet:

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

(The whole Oracle provider API is a pile of junk IMHO).

edited May 8, 2018 at 13:25

answered Nov 22, 2016 at 14:58

Gary McGill

27.8k26 gold badges126 silver badges210 bronze badges

Collectives™ on Stack Overflow

Proper Way To Use .Net DateTime in Parameterized SQL String

4 Answers 4

7 Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related