none
Not a valid month error (ORA-1843) - Oracle "timestamp" column vs .NET DateTime

    Question

  • 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)
      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 (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
    Thursday, April 16, 2009 11:37 AM

Answers

  • The parameter value should be a Date data value and not a string. There is no need to format the date when you're passing it in as a parameter since the underlying representation is numeric.

    The native Oracle Date data type corresponds to the .NET System.DateTime data type. I don't have ODP.NET installed, but doesn't that library have its own set of parameter data types (OracleDbType.Date maybe)? You should be using whatever parameter data type represents a Date.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, April 24, 2009 1:12 PM

All replies

  • I see you posted to the Oracle SQL & PL/SQL forum.  Have you also posted your question to the Oracle ODP.NET forum as well?

    http://forums.oracle.com/forums/forum.jspa?forumID=146


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, April 23, 2009 9:42 PM
  • The parameter value should be a Date data value and not a string. There is no need to format the date when you're passing it in as a parameter since the underlying representation is numeric.

    The native Oracle Date data type corresponds to the .NET System.DateTime data type. I don't have ODP.NET installed, but doesn't that library have its own set of parameter data types (OracleDbType.Date maybe)? You should be using whatever parameter data type represents a Date.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, April 24, 2009 1:12 PM
  • Thanks Paul!

    This did the trick. The difference between System.Data.Oracle and Oracle.DataClient is that ODP.NET is more rigid in it's type setting. If you are passing the date parameter in as a string into the PL/SQL call, it will barf (technical term). You need to cast the value back to a date time.

    string strDateSent = System.DateTime.Now().ToString()

    bad:
    da.SelectCommand.Parameters.Add("i_sentDt", OracleDbType.Date).Value = strDateSent

    Good:
    da.SelectCommand.Parameters.Add(
    "i_sentDt", OracleDbType.Date).Value = Convert.ToDateTime(strDateSent)

    One of those oddities of moving from the MS Oracle library to ODP.NET.

    Tuesday, May 26, 2009 7:19 PM