More fun with ODP.NET - Tried different format string none worked!!
My table column is defined as:
CreateDate timestamp default sysdate NOT NULL
This is first thing I tried (most intuitive right?), it don't work ...
oParam.DbType = DbType.DateTime;
oParam.Value = DateTime.Now;
Error message was: +"ORA-01858: a non-numeric character was found where a numeric was expected"} System.Exception {Oracle.DataAccess.Client.OracleException}+
So I tried many things... no luck
oParam.Value = DateTime.Now.ToString("dd-MMM-yy"); // HH:m:ss
oParam.Value = DateTime.Now.ToString("dd-MM-yy");
oParam.Value = DateTime.Now.ToString("MM-dd-yy");
oParam.Value = DateTime.Now.ToString("MM/dd/yy");
oParam.Value = DateTime.Now.ToString("dd/MM/yy");
Also tried different combinations using MMM, NO LUCK!!
Also should I use:
oParam.DbType = DbType.*DateTime*; --> Error: Not a valid month error (ORA-1843)
or
oParam.DbType = DbType.*String*; --> Error "ORA-01858: a non-numeric character was found where a numeric was expected"
when I'm passing in a date time string as supposed to a DateTime object?
btw - using MMM means month is non-numeric.
CASE 1: dd-MMM-yy ---> 16-Apr-09
CASE 2: dd-MMM-yy hh:mm:ss.ffff tt ---> 16-Apr-09 08:50:46.6093 PM
In both cases, you'd end up with:
*ORA-01858: a non-numeric character was found where a numeric was expected"} System.Exception {Oracle.DataAccess.Client.OracleException}*
I then Checked db date format via:
select sysdate from dual;
SYSDATE
16-APR-09
1 row selected.
Last trick - Works but UGLY - use to_date function in my sql statement + string concatenation instead of parameter!!!
select to_date('16-4-09', 'dd-mm-yy') today from dual
*// More special attention for Oracle provider*
if (oContext.DefaultDBProvider == DBUtil.DataProvider.OracleODAC || oContext.DefaultDBProvider == DBUtil.DataProvider.OracleProvider)
{
strSQL_insert = strSQL_insert.Replace(ORACLE_CREATEDATE, "to_date('" + DateTime.Now.ToString("dd-MM-yy") + "', 'dd-MM-yy')");
}
*// If not oracle, we can do it the proper way!!*
else
{
oParam = oCmd.CreateParameter();
oParam.ParameterName = DBUtil.FixParameterNameForOracle(oContext.DefaultDBProvider, "@CreateDate");
oParam.Direction = ParameterDirection.Input;
oParam.DbType = DbType.DateTime;
oCmd.Parameters.Add(oParam);
}
Any suggestion? I've isolated the one offending parameter but need the right "format" and ora messages is not helpful at all.
I can get by concatentating SQL but can't get INSERT to work with IDataParameter (DateTime.Now or DateTime.Now.ToString(of various flavours)!
REF:
633236
http://msdn.microsoft.com/en-us/library/8kb3ddd4.aspx