Problems Retrieving the Oracle TimeStamp Field in .Net

Question

I need to begin working with milliseconds in .Net 3.0. The data will be returned from an Oracle Database and if I understand correctly Oracle can only store dates that use milliseconds as a TimeStamp. It does appears that the Date type in .Net can handle milliseconds but when I have tried to retrieve Timestamps from Oracle stored procedures in the past I ran into a nasty error. Can .Net handle the Oracle timestamp data type or do I need to bring it back as a VarChar and cast it to a date type?

Thanks,

Dave

Answer 1

It would be helpful to know what the error is..

I had a similar problem with linking Oracle tables in Access. It uses a time stamp that is days (with fractional days) since midnight 1/1/1970. I just bring the fields in as Decimal, and convert them:

field/86400+25569

Then you can use format() or whatever function to convert it to a date string.

Answer 2

If you use ODP.NET, then you can get the value as an Oracle.DataAccess.Types.OracleTimeStamp. You can then use the Value property to return it as a System.DateTime object.

Your code will look something like this:

    Private Function GetTimeStamp() As DateTime

        Dim timeStamp As DateTime

        Dim sql As String = "your timestamp query"

        Using conn As OracleConnection = New OracleConnection("your connection string")
            Using command As OracleCommand = New OracleCommand(sql, conn)
                conn.Open()

                Using dataReader As OracleDataReader = command.ExecuteReader(CommandBehavior.CloseConnection)
                    If dataReader.Read() Then
                        timeStamp = dataReader.GetOracleTimeStamp(0).Value
                    End If
                End Using

            End Using
        End Using

        Return timeStamp

    End Function