Solved

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

Posted on 2004-09-02
9
269 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
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…

739 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