[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

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
0
jacoulter
Asked:
jacoulter
  • 5
  • 4
1 Solution
 
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
 
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
Bob LearnedCommented:
Try this:

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

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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now