Not a valid month asp.net / oracle

Question

I have two values that have dates inside in the format dd/MM/yyyy and am trying to get the dates in between these two values. The values look like these(17/06/2013) in ONE and TWO variables. And then I send from my asp to an oracle procedure to execude a query and i get ORA-01843 not a valid month. Ive searched for this error but none of the solutions seemed to work for me. The thing is that this code used to work, I didnt change anything on these code and now it doesnt work for some reason.

Here is the code from my asp:

ONE = fromDate.Value
TWO = toDate.Value
Generic.searchBetweenDates(ONE, TWO, dt)


Public Function searchBetweenDates(ByVal PFROMDATE As String, ByVal PTODATE As String, ByRef PUSERINFO As DataTable)
        Dim myDataTable As New DataTable
        Dim myDataAdapter As New OracleDataAdapter

        Dim oraCmd As New OracleCommand("cantine_test.searchbetweendates")
        oraCmd.CommandType = System.Data.CommandType.StoredProcedure
        oraCmd.Parameters.Add(New OracleParameter("PFROMDATE", OracleType.VarChar, 30)).Value = PFROMDATE
        oraCmd.Parameters.Add(New OracleParameter("PTODATE", OracleType.VarChar, 300)).Value = PTODATE
        oraCmd.Parameters.Add(New OracleParameter("PUSERINFO", OracleType.Cursor)).Direction = ParameterDirection.Output

        Dim oConn As New OracleConnection(ConnectionString)
        Try
            oConn.Open()
            oraCmd.Connection = oConn
            cleanParams(oraCmd.Parameters)

            myDataAdapter.SelectCommand = oraCmd
            myDataAdapter.Fill(myDataTable)
            If Not myDataTable Is Nothing Then
                PUSERINFO = myDataTable 'return reference using byref param
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            oraCmd.Dispose()
            oConn.Close()
            myDataAdapter.Dispose()
        End Try
        Return PUSERINFO
    End Function

And here is the code from my Oracle procedure:

PROCEDURE searchbetweendates
   (PFROMDATE IN VARCHAR2, PTODATE IN VARCHAR2, PUSERINFO OUT SYS_REFCURSOR)
   AS

BEGIN
    OPEN PUSERINFO FOR

        SELECT ORDERDATE,ORDERID,ORDERTIME,ORDERLIST,QUANTITY,ITEMPRICE,ORDERPRICE,LOCATION
        FROM ORDERSDETAIL 
        WHERE ORDERDATE >= to_date(PFROMDATE, 'dd/MM/yyyy')
        AND ORDERDATE <= to_date(PTODATE,'dd/MM/yyyy');

END;

Any ideas?

Are you sure that fromDate.Value will give you the date in correct format? Debug and check the format of the string you pass to Oracle. — GTG
There is another strange thing there. Generic.searchBetweenDates(fromDate.Value, toDate.Value, dt) this gives me a different format '#6/17/2013#{Date}' And when it goes to the other class here: Public Function searchBetweenDates(ByVal PFROMDATE As String, ByVal PTODATE As String, ByRef PUSERINFO As DataTable) the PFROMDATE and PTODATE have correct values like '17/06/2013'. I tried sending both fromDate.Value and ONE and modified my procedure but still the same result. — Antonis Lambrianides

APC APC · Accepted Answer · 2013-06-25T10:00:25

Oracle hurls ORA-01843 when we pass a string which doesn't match the specified date format: for instance when the string has the US format and the format mask doesn't:

SQL> select to_date('01/13/2013', 'dd/mm/yyyy') from dual
  2  /
select to_date('01/13/2013', 'dd/mm/yyyy') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month


SQL>

So, the most likely explanation is that you are passing values which you think have a common format but in fact don't. This is fantastically common when "dates" are stored as strings. In other words, this is a debugging question.

Although, one thought occurs: is ORDERSDETAIL.ORDERDATE itself of a DATE datatype.

#6/17/2013#{Date} is the sort of string which will definitely lead to an ORA-1843 error. So you need to trace your code to discover where it comes from.

"how should i check if the value in my columns are correct"

Debugging dot Net is not my strong point. However, there are traditionally two ways. One is to step through the code in a debugging tool. If you're using Visual Studio or a similar IDE you should be able to do this. The other suggestion is to embed logging commands in your code and write trace messages e.g. to a file.

Which approach suits you best? Only you can tell.

Not a valid month asp.net / oracle

1 Answers