Oracle 10g timestamp field

[Former Member]

Hi,

We are using BO XIR3.1 and just came across an issue whereby when using a Oracle Timestamp field in a query filter, we get an oracle error of cannot of invalid month.  The only way I see of resolving this is by converting the value to a MM/DD/YYYY HH:MI:SS in the Object SQL (e.g. TO_DATE(TO_CHAR(TIMESTAMP_COLUMN,'MM/DD/YYYY HH:MI:SS'),'MM/DD/YYYY HH:MI:SS')) .  Is there a way of resolving this without adding this extra logic to the Object SQL?  I know it used to be an issue with XIR2 but I am wondering if its resolved with XIR3.1.

Thanks

Nilesh

[former_member196119]

Hello

I remember having had this problem also, that was due to a referenced oracle java ODBC driver error, which was badly encoding/decoding timestamps.

Some encoded dates are then not decodable (like monthes > 12). I had found a little sql script able to find which records where not good, by getting record as a dump and decoding it manually.

Well if you can't upgrade Oracle, my best solution was then to directly change the older dates to 01/01/2000 on the source table as the problem was appearing to dates like 1970. (direct sql statements where ok, but not DS loads as far as I remember it).

Regards

[Former Member]

Hi Nilesh,

Can you paste the code of the query filter and the snpshot of the error?

-Prathamesh