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

The Dates come out correct when watching in a debugger, and if it was a problem with the result of ToShortDateString() than it would have the same issue whether there was one or two date parameters, but since if I remove one, or the other date parameter, and run it with just one of the parameters it works fine. — UnHoly Ram, Jan 09 '14 at 16:45
So could you see what's the sql generated with both dates ? Example here : http://stackoverflow.com/questions/265192/get-the-generated-sql-statement-from-a-sqlcommand-object — Raphaël Althaus, Jan 09 '14 at 16:50
The sql is as expected (Although I had to add the single quotes around the dates: SELECT * FROM TABLE WHERE FIELD = '1099' AND CREATED_DT >= TO_DATE('1/1/2014', 'MM/DD/YYYY') AND CREATED_DT <= TO_DATE('12/31/2014', 'MM/DD/YYYY') AND SSN = 'ssnNumber' — UnHoly Ram, Jan 09 '14 at 17:02

Raphaël Althaus · Accepted Answer · 2014-01-09T17:00:08.827

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;

edited Jan 09 '14 at 17:00

answered Jan 09 '14 at 16:25

Raphaël Althaus

59,727
6
96
122

The Dates come out correct, and if it was a problem with the result of ToShortDateString() than it would have the same issue whether there was one or two date parameters, but since if I remove one, or the other date parameter, and run it with just one of the parameters it works fine. – UnHoly Ram Jan 09 '14 at 16:43
@UnHolyRam I found something else, could you give a try ? – Raphaël Althaus Jan 09 '14 at 17:00
We have a winner. Thanks – UnHoly Ram Jan 09 '14 at 17:37

score 0 · Answer 2 · answered Jan 09 '14 at 16:25

0

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...

answered Jan 09 '14 at 16:25

Oleg Sklyar

9,834
6
39
62

Guntram Blohm · Answer 3 · 2014-01-09T16:35:55.113

0

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".

edited Jan 09 '14 at 16:35

answered Jan 09 '14 at 16:29

Guntram Blohm

9,667
2
24
31

DateTime in c# working with 0 to 11 ? Really ? Where does this come from ? – Raphaël Althaus Jan 09 '14 at 16:33
Looked like Java to me, and the OP didn't provide a language tag. Removed that part of my answer. – Guntram Blohm Jan 09 '14 at 16:34

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

3 Answers3