Why do I get an "Invalid month" error when storing a date in an Oracle database using C#?

Question

I am trying to update the Oracle table using C#. I have written one function

public int UpdateInfo(String emplid, bool i4, char?status,String bdate)
{
    int result = -1;
    try
    {
        using (OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["OracleOleDB"].ConnectionString))
        {
            conn.Open();
            OracleCommand cmdN = conn.CreateCommand();
            cmdN.CommandText= "UPDATE INFO SET I4=:i4,Status=:status,Bdate=(SELECT TO_DATE(:bdate,'DD_MON_RR'))where EMPLID =:emplid;";
            cmdN.Parameters.Add(":I4",i4);

            cmdN.Parameters.Add(":Status", status);
            cmdN.Parameters.Add(":EMPLID",emplid);
            cmdN.Parameters.Add(":Bdate", bdate);

            //below code I tried as well
            //  OracleCommand cmd = new OracleCommand("UPDATE INFO SET I4 = :I4,Status=:Status,Bdate=TO_DATE(:Bdate,'DD_MON_RR','NLS_DATE_LANGUAGE,AMERICAN') WHERE EMPLID =:EMPLID;", conn);
            //cmd.Parameters.Add(":I4", OracleDbType.Char).Value = (i4 ? 'Y' : 'N');
            //cmd.Parameters.Add(":Status", OracleDbType.Char).Value = status;
            //cmd.Parameters.Add(":EMPLID", OracleDbType.Varchar2).Value = emplid;
            //    cmd.Parameters.Add(":Bdate", OracleDbType.Date).Value = Bdate;

            result = cmdN.ExecuteNonQuery();
            conn.Close();
        }

    }

I am getting an error Invalid month. Initially I was getting an error Invalid data so Commented out that code.

If I run the same query directly out of Visual Studio it stores the data. I use below query out of Visual studio

UPDATE INFO SET Status='S',Bdate=TO_DATE('01-June-2017','DD_MON_RR') WHERE EMPLID =00016106;

Looking for help to fix this.

Please take the time to properly format your code before submitting a question. Nobody wants to work with the mess you dropped. — spender, Jun 01 '17 at 16:08
The problem is that you are trying to pass a string instead of a date. Remove `TO_DATE`, change the type of the `bdate` method parameter to DateTime and just use it as it is — Panagiotis Kanavos, Jun 01 '17 at 16:09
You should always use the *correct* data type. Using strings instead of dates, integers, decimals just begs for conversion errors and harms performance. If that conversion appeared in a `WHERE` clause you could force a slow full table scan instead of a fast index seek — Panagiotis Kanavos, Jun 01 '17 at 16:12

score 2 · Answer 1 · answered Jun 01 '17 at 16:18

Pass the actual date to the method instead of a string, and use the date value as a parameter without any conversions, ie:

public int UpdateInfo(...,DateTime bdate)
{
    cmdN.CommandText= "UPDATE INFO SET I4=:i4,Status=:status,Bdate=:bdate where EMPLID =:emplid;";            
    ...
    cmdN.Parameters.Add(":Bdate", bdate);
    ...
}

One of the most important advantages of query parameters is that you can pass an actual strongly-typed value instead of converting the decimal or date to a string and having to struggle with localization and formats.

Another important factor is that using functions like TO_DATE on a WHERE clause prevents the database from using indexes. The database has to scan all rows to calculate the function results before filtering them.

score 0 · Answer 2 · answered Jun 01 '17 at 16:28

What worked for me was using a parameter to pass date, and pre-formatting it in c#.

example...mind the syntax, my stored procedure parameters are built from dictionary

{"p_MSG_DATE", System.DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss") }

in stored procedure

RCV_DATE = TO_DATE(p_MSG_DATE, 'MM/DD/YYYY HH24:MI:SS')

Why do I get an "Invalid month" error when storing a date in an Oracle database using C#?

2 Answers2