[RESOLVED] Data type not supported?

[Maverickz]

I am trying to pull some dates out of an Oracle DB but as soon as I try to execute the query I get an error that the "Data type is not supported". I know that the problem is that the date format in the DB is not a valid Date/Time format for VB but I have no idea how to force VB to load it as a String into the record set. The format of the date/time in Oracle is:
7/14/2010 3:01:48.483624 AM
Note the sub-second times. This is what is causing the problem. Unfortunately this is the exact part of the data I need. So the question is how can I pull these dates into VB6?

[RhinoBull]

Try using TO_CHAR function - here is a quick sample:

SELECT TO_CHAR(your_date_field, 'MM/DD/YYYY') FROM your_table

[baja_yu]

Rhino, I'm guessing this is a typo: 'MM/DD/YYYY" (note the single and double quotes)

[RhinoBull]

Yep, thanks ...

[dilettante]

It sounds like you may actually be talking about Oracle's TIMESTAMP data type.


Part 1.

It looks like you'll need to use Oracle's "OraOLEDB" Provider instead of the client provider supported by Microsoft. Starting around the Oracle 8 to 9i transition Oracle added new features that Microsoft chose not to support directly via ADO, hoping that everyone would be drinking the .Net Kool-Aid.

Oracle's Provider handles TIMESTAMP as follows:

TIMESTAMP Datatypes

This section discusses the Timestamp datatypes and then provides the following:

•Sample data illustrating insert and retrieval operations using each of the Timestamp datatypes.
•A Visual Basic code example using the Timestamp datatypes.

Timestamp datatypes are mapped to the OLE DB DBTYPE_DBTIMESTAMP. The OLE DB DBTYPE_DBTIMESTAMP datatype does not have TIME ZONE information

The Timestamp datatypes include:

•TIMESTAMP
•TIMESTAMP WITH TIME ZONE
•TIMESTAMP WITH LOCAL TIME ZONE

ADO Consumers

For the Timestamp datatypes, ADO consumers must specify the CursorLocation as adUseServer and use Recordset for DML operations.

Features of OraOLEDB


Part 2.

DBTYPE_DBTIMESTAMP in OLEDB normally maps to ADO's DataTypeEnum value adDBTimeStamp (135), which becomes a VB6 Date if you use conventional techniques. This won't help you.

However we have alternatives:

How To Get Fractions of a Second from ADO adDBTimeStamp Field



Note that both parts of this appear to be required. In other words you need to (1.) use Oracle's OLDB Provider (not Microsoft's Provider for Oracle) with an ADO Recordset using a server-side cursor, and (2.) use the Decimal datatype technique.

You cannot use SQL DML, you must use a Recordset.

[Maverickz]

Actually RhinoBull's response allowed me to actually pull the data in. Thanks!

I do still have one more problem with the Query but I am going to mark this resolved and start a new thread. Thanks again guys.