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

Question

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;
            }
        }

Answer 1

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;

Answer 2

You expect date formatted as MM/DD/YYYY, but it is not guaranteed that ToShortDateString() returns it in this format. A format specifiction is needed. But well, I do not even know what is the programming language you are using to provide further help...

Answer 3

Print out the results of ToShortDateString and you'll see what happens. Also, i agree with "you should provide a format because you can't rely on the default".