picasothakkar
asked on
Display / Use Timestamp data type from Oracle into crystal report
Hi,
I am using crystal report 8.5 and my database is Oracle 8i.
I have a field of type "timestamp" in one of the table defined as "DATETIME, TIMESTAMP(6), 2147483647"
When I try to include this in my report, although the field gets included, it gives me ODBC error generated by oracle of incompatible types followed by crystal report error saying error detected in dll. When i rightclick on the field it shows : Blob Field: "tablename.datetime" followed by other options.
Can anybody throw some light please?
Best Wishes,
Picaso
I am using crystal report 8.5 and my database is Oracle 8i.
I have a field of type "timestamp" in one of the table defined as "DATETIME, TIMESTAMP(6), 2147483647"
When I try to include this in my report, although the field gets included, it gives me ODBC error generated by oracle of incompatible types followed by crystal report error saying error detected in dll. When i rightclick on the field it shows : Blob Field: "tablename.datetime" followed by other options.
Can anybody throw some light please?
Best Wishes,
Picaso
It would be better if you post here your table.
ASKER
Hi,
As per suggestion from mlmcc, i am in process to get the field name "DATETIME" changed to "LOGDATETIME". Here's the current schema that is giving problem:
[Row, Column, Type, Length]
1 UNIQUEID, DECIMAL, 10,
2 REQUESTEDBY, VARCHAR2, 25,
3 DATETIME, TIMESTAMP(6), 2147483647,
4 RETURNCODE, VARCHAR2, 50,
5 TRANTYPE, VARCHAR2, 10,
6 GLOBALUSER, VARCHAR2, 25,
7 OUTCOME, VARCHAR2, 10,
8 ERRORMSG, VARCHAR2, 300,
Thanks in advance,
Picaso
As per suggestion from mlmcc, i am in process to get the field name "DATETIME" changed to "LOGDATETIME". Here's the current schema that is giving problem:
[Row, Column, Type, Length]
1 UNIQUEID, DECIMAL, 10,
2 REQUESTEDBY, VARCHAR2, 25,
3 DATETIME, TIMESTAMP(6), 2147483647,
4 RETURNCODE, VARCHAR2, 50,
5 TRANTYPE, VARCHAR2, 10,
6 GLOBALUSER, VARCHAR2, 25,
7 OUTCOME, VARCHAR2, 10,
8 ERRORMSG, VARCHAR2, 300,
Thanks in advance,
Picaso
ASKER
I got the field name changed to LOGDATETIME but that still dosen't solve the problem. Any second thoughts?
In Oracle there cannot be a column of type TIMESTAMP - see this (the registration is free):
http://download-west.oracle.com/docs/cd/A87860_01/doc/appdev.817/a76939/adg04typ.htm#427628
So correct it to:
LOGDATETIME DATE
http://download-west.oracle.com/docs/cd/A87860_01/doc/appdev.817/a76939/adg04typ.htm#427628
So correct it to:
LOGDATETIME DATE
ASKER
i don't know which version of oracle i am using but i do get a resultset when i execute the sql query (select to_char(logdatetime) from loginfo) gives results as:
[Row, TO_CHAR(LOGDATETIME)]
1 20-SEP-04 06.24.54.000000 PM,
2 20-SEP-04 09.11.55.000000 PM,
3 20-SEP-04 05.13.13.000000 PM,
4 20-SEP-04 05.44.27.000000 PM,
5 20-SEP-04 10.47.53.000000 AM,
6 20-SEP-04 10.48.18.000000 AM,
7 20-SEP-04 10.48.31.000000 AM,
8 20-SEP-04 09.18.22.000000 AM,
9 20-SEP-04 10.08.13.000000 AM,
10 20-SEP-04 10.54.37.000000 AM,
11 20-SEP-04 10.56.19.000000 AM,
........................
Should i use date datatype? does it include time also?
[Row, TO_CHAR(LOGDATETIME)]
1 20-SEP-04 06.24.54.000000 PM,
2 20-SEP-04 09.11.55.000000 PM,
3 20-SEP-04 05.13.13.000000 PM,
4 20-SEP-04 05.44.27.000000 PM,
5 20-SEP-04 10.47.53.000000 AM,
6 20-SEP-04 10.48.18.000000 AM,
7 20-SEP-04 10.48.31.000000 AM,
8 20-SEP-04 09.18.22.000000 AM,
9 20-SEP-04 10.08.13.000000 AM,
10 20-SEP-04 10.54.37.000000 AM,
11 20-SEP-04 10.56.19.000000 AM,
........................
Should i use date datatype? does it include time also?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I converted the field to date and things work fine now. My oracle db is 8.0. Thanks henka
If so I suspect there is a problem with either Crystal or the ODBC driver or both because DATETIME is a datatype in Crystal and may also be one in the ODBC driver.
mlmcc