oracle error 1843,Not a valid month error (ORA-1843) - Oracle "timestamp" column vs .NET DateTime-优快云博客

More fun with ODP.NET - Tried different format string none worked!! wasted couple of hours!!

My table column is defined as:

CreateDate timestamp default sysdate NOT NULL,

This is first thing I tried (most intuitive right?), This don't work ...

oParam.Value = DateTime.Now;

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)

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 (It 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)<

// Resort to string concatenation!! Just for Oracle!!

(As supposed to IDataParameter)

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.

REF:

http://forums.oracle.com/forums/thread.jspa?threadID=631236

http://msdn.microsoft.com/en-us/library/8kb3ddd4.aspx