In what format does Oracle expect a date value, and how can I give Oracle what it wants?

Question

"AVAILABLEDATE" is a column of type DATE.

I can query the table via Toad and get results. However, in (Winforms/C#/dotConnect) code, it's not working.

ocmd.Parameters.Add("AVAIL_DATE", getDateToQuery());

I'm pretty sure the problem is the way I'm passing the date:

private DateTime getDateToQuery() {
  DateTime candidateVal = dateTimePickerScheduleDate.Value;
  if (candidateVal.Equals(null)) {
    candidateVal = DateTime.Now;
  }
  return candidateVal;
}

...but I don't know how to force the date value to be in the format Oracle will recognize.

What is the select statement you have put into the command? The parameter markers are different for .NET and Toad. — davisoa, Apr 23 '12 at 17:48
No err msg, just wasn't returning any vals; fixed now - see below. — B. Clay Shannon-B. Crow Raven, Apr 23 '12 at 18:11

score 4 · Answer 1 · answered Apr 23 '12 at 17:57

4

Try adding the parameter type:

OracleParameter p1 = new OracleParameter("AVAIL_DATE", OracleDbType.Date);
p1.Value = getDateToQuery();
ocmd.Parameters.Add(p1);

Also, make sure you provide the parameters in order, the last time I worked with Oracle I remember the names of the parameters were ignored.

answered Apr 23 '12 at 17:57

Ulises

13,229
5
34
50

Seems elegant, but it doesnt' work - my kludgy way works, though...? – B. Clay Shannon-B. Crow Raven Apr 23 '12 at 18:16
@ClayShannon other than the type I don't see what the difference is, did you change anything else? perhaps your function getDateToQuery() was not returning the value you needed? – Ulises Apr 23 '12 at 18:20

score 0 · Accepted Answer · answered Apr 23 '12 at 18:10

This works, but I'm not sure it's the best way:

int iFromYear = dateTimePickerScheduleDate.Value.Year;
int iFromMonth = dateTimePickerScheduleDate.Value.Month;
int iFromDay = dateTimePickerScheduleDate.Value.Day;
. . .
ocmd.Parameters.Add("AVAIL_DATE", new DateTime(iFromYear, iFromMonth, iFromDay));

In what format does Oracle expect a date value, and how can I give Oracle what it wants?

2 Answers2