Solved

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

Posted on 2004-09-02
9
267 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:jacoulter
  • 5
  • 4
9 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 11964622
Try something like this:

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

Bob
0
 

Author Comment

by:jacoulter
ID: 11964853
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
 
LVL 96

Expert Comment

by:Bob Learned
ID: 11964926
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 11965004
Try this:

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

Bob
0
 
LVL 96

Expert Comment

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

Bob
0
 

Author Comment

by:jacoulter
ID: 11965113
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
 

Author Comment

by:jacoulter
ID: 11965173
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
 
LVL 96

Expert Comment

by:Bob Learned
ID: 11965209
Did you try the code that I came up with?

Bob
0
 

Author Comment

by:jacoulter
ID: 11965322
Perfect - your code did it.

Thank you very much!

Jim
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question