sqloracle-databasedatetimeoraclecommand

2nd Date Parameter Throws ORA-01843: not a valid month error


I have a query where I need to check a date between two dates using Oracle. Whenever I run the code I get an ORA-01843: not a valid month error. However whenever I remove either of the two parameters from the sql it works fine, but trying to use two date parameters throw an error. What am I missing?

        StringBuilder sql = new StringBuilder();
        DateTime yearBegin = new DateTime(Convert.ToInt32(taxYear) + 1, 1, 1);
        DateTime yearEnd = new DateTime(Convert.ToInt32(taxYear) + 1, 12, 31);
        sql.Append(
            "SELECT * FROM TABLE WHERE FIELD = '1099' AND CREATED_DT >= TO_DATE(:createdYearBegin, 'MM/DD/YYYY') AND CREATED_DT <= TO_DATE(:createdYearEnd, 'MM/DD/YYYY') AND SSN = :ssn");
        try
        {
            using (OracleConnection cn = new OracleConnection(ConfigurationManager.AppSettings["cubsConnection"]))
            using (OracleCommand cmd = new OracleCommand(sql.ToString(), cn))
            {
                cmd.Parameters.Add("ssn", ssn);
                cmd.Parameters.Add("createdYearBegin", yearBegin.ToShortDateString());
                cmd.Parameters.Add("createdYearEnd", yearEnd.ToShortDateString());
                cn.Open();
                OracleDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                ret = dr.HasRows;
            }
        }

Solution

  • think you've got a problem with your parameter's order.

    If you don't bind parameters by name, they are bound by position (means the order in which you add parameters is taken).

    Just try to add :

    cmd.BindByName = true;