ODP.NET

please advice,

currently i have problem with date time query using odp.net in c#. what should we change in date time conversion to make it works?

the problem is query always get error if i select date time picker at "October" month, the rest months i.e. January, September query is successful.

oracle.data.access.dll version that i used = 2.111.6.20.

Environment server oracle (Error ORA-01858) :

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
"CORE 10.2.0.3.0 Production"
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

My Script :

string _sql;
string date1 = datetimePicker1.Text;
string date2 = datetimePicker2.Text;
try
{
OracleConnection konek = new OracleConnection(db.setting);
_ds = new DataSet();
konek.Open();
sql = "select T.costcentre, '$'||SUM((T.QTYOUT)*S.price) AS PRICE FROM stock_transaction T, stock_master S where T.id = S.id AND to_date(T.date_transaction, 'DD-MM-YY') between '"
+ date1 + "' and '" + date2 + "' group by T.costcentre";
OracleDataAdapter adapterObj = new OracleDataAdapter(_sql, konek);

adapterObj.Fill(_ds);
konek.Close();
konek.Dispose();
konek = null;
dataGridView2.DataSource = _ds.Tables[0];
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}

FYI, this script is working fine at my Oracle EXpress edition, with environment server :

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
"CORE 10.2.0.1.0 Production"
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production


Thanks,

Hadi