Hello,

I cannot figure out why I am recieving this error. For one SQL statement, the code runs fine, then when a different date is selected, the code fails. Below is the code and the SQL statements:

[Function that retrieves the value]

    Public Shared Function CheckDateDifference(ByVal SQL)
        Dim returnValue = Nothing

        Dim conn As New OracleConnection(Connection.GetConn())
        Dim comm As New OracleCommand
        Dim dr As OracleDataReader
        Dim err As String = ""

        Using conn
            Try
                conn.Open()

                comm.Connection = conn
                comm.CommandType = Data.CommandType.Text
                comm.CommandText = SQL

                dr = comm.ExecuteReader

                Do While dr.Read
                    returnValue = dr("ANSWER")
                Loop
            Catch ex As Exception
                err = ex.Message
                returnValue = "ERROR"
            Finally
                conn.Close()
            End Try
        End Using

        returnValue = returnValue.ToString()

        Return returnValue
    End Function

[/end function example]

[SQL 1 - Returns valid value - code is successful]

Dim SQL As String = "SELECT MONTHS_BETWEEN(sysdate, to_date('07/12/07 10:31:37 ', 'mm/dd/yy hh12:mi:ss')) AS ANSWER FROM REQUEST WHERE ReqID=629"

[/end SQL1]

[SQL 2 - Code Fails - No value returned]

Dim SQL As String = "SELECT MONTHS_BETWEEN(sysdate, to_date('06/11/07 10:31:37 ', 'mm/dd/yy hh12:mi:ss')) AS ANSWER FROM REQUEST WHERE ReqID=5338"

[/end SQL2]

Why is it that 1 month makes a difference is the code failing? My only assumption is that the returned value has too many decimal places for a VB.NET variable to hold. If so, is there a way to get around this? I am only wanting to know if the date is over 30 days past todays date.

Please help me find a solution. Thanks,
KJAK

Alright!

It was due to the fact that there were too many decimal places in the result. I used the Oracle ROUND function to eliminate unneeded decimal places. The end SQL looks similar to this:

Dim SQL As String = _ 
"SELECT ROUND(MONTHS_BETWEEN(sysdate, " _
to_date('06/11/07 10:31:37 ', 'mm/dd/yy hh12:mi:ss')), 4) " _
AS ANSWER FROM REQUEST WHERE ReqID=5338"

This works perfectly and I haven't had the error appear once since implementing.