?
Solved

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

Posted on 2004-09-02
9
Medium Priority
?
272 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 96

Accepted Solution

by:
Bob Learned earned 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

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…
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 …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

770 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