Read an Oracle DATE type and convert to .NET DateTime type

I am want to read an Oracle column of type DATE and convert it into a .NET DateTime object.

I wrote a small console app to test my code in:

            strSQL = "SELECT time_stamp FROM action_tbl WHERE status = 'WAITING'"

            oledbselect1 = New OleDb.OleDbCommand(strSQL, oledbconnect1)

            dbRdr = oledbselect1.ExecuteReader()
            While dbRdr.Read()
                hour = dbRdr.Read.toString()
                Console.WriteLine(hour)
                Console.ReadLine()
            End While

This works, but I'm not seeing a string that looks like a date/time. Instead it comes back with "TRUE" for every record containing a value in the time_stamp column.

I have tried dropping the .toString() method and get the same result.

I am trying to convert to the .NET DateTime type so I can use the DateDiff method.

TIA,

Jim
jacoulterAsked:
Who is Participating?
 
Bob LearnedCommented:
Try this:

While dbRdr.Read()
   Dim hour As DateTime = dbRdr("TimeStamp")
   Console.WriteLine(hour)
End While

Bob
0
 
Bob LearnedCommented:
Try something like this:

Dim hours As DateTime = DateTime.Parse(dbRdr.Read.ToString)

Bob
0
 
jacoulterAuthor Commented:
It doesn't like that -

"System.FormatException: The string was not recognized as a valid DateTime.  There is a unknown word starting at index 0.
   at System.DateTimeParse.Lex(Int32 dps, __DTString str, DateTimeToken dtok, DateTimeRawInfo raw, DateTimeResult result, DateTimeFormatInfo& dtfi) <etc>

I'm guessing the DateTime.Parse() method is choking on the "TRUE" string value I've been seeing. . .
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Bob LearnedCommented:
Sorry, you did say that.  What should the value of time_stamp be returning?

An aside, you can use the OracleClient if you have VS.NET 2003, instead of the OleDb client.

Bob
0
 
Bob LearnedCommented:
I wasn't even thinking at first.  dbRdr.Read returns a true to indicate that there are still records to process.

Bob
0
 
jacoulterAuthor Commented:
I'm not sure what value time_stamp returns when accessed by the oleDb client.

In my Oracle table, time_stamp is of type DATE.

I can access it via SQL*Plus and can insert values into it using the oleDb client, but I can't seem to read it into a VB.NET string as it is represented in SQL*Plus, i.e. '01-SEP-04'

I'm guessing VB.NET sees all values stored in an Oracle record as strings , but the TRUE and FALSE values I've been seeing lead me to believe that something in the DataReader object dbRdr is is merely checking for the existence of a value in the record and returning the TRUE/FALSE value. . .

Unfortunately I'm using VS.NET 2002 so the OracleClient isn't available. . .

I tried reading a column of type VARCHAR into my VB.NET string variable and got the same True/False result. . .


0
 
jacoulterAuthor Commented:
Ooops!

the oleDataReader.Read() method returns a boolean.

That's what it's supposed to do.

Looks like I need to use the oleDataReader.GetString() method, but I can't seem to access it.

I must be missing an Import. . .
0
 
Bob LearnedCommented:
Did you try the code that I came up with?

Bob
0
 
jacoulterAuthor Commented:
Perfect - your code did it.

Thank you very much!

Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.