无效月份ASP.NET/oracle

Question

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

下面是我的Oracle过程中的代码：

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;

有什么想法吗？

Answer 1

当传递与指定日期格式不匹配的字符串时，Oracle会抛出ORA-01843：例如，当字符串具有美国格式而格式掩码不匹配时：

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> 

所以，最有可能的解释是，你传递的值，你认为有一个共同的格式，但实际上没有。当“日期”存储为字符串时，这是非常常见的。换句话说，这是一个调试问题。

不过，有一个想法出现了：OrdersDetail.OrderDate本身是一个日期数据类型。