Page 1 of 1
Posted: Wed Dec 18, 2019 4:52 am
by chulett
Check for fixpacks / patches for your version that you haven't applied yet. Open a support case. Or both.
Posted: Wed Dec 18, 2019 5:20 am
by thurmy34
In fact the problem comes from a fals date (00/01/3689) in a timestamp column.
To handle that i have to declare the column in varchar(10).
Thanks
Posted: Wed Dec 18, 2019 7:54 am
by PaulVL
Did you use the schema from the table definition as found on that Oracle box or did you hand code it? I doubt any properly defined date column on Oracle would reflect a 00 month/day value.
Posted: Thu Dec 19, 2019 1:26 am
by thurmy34
The following sql
Code: Select all
select ID,TO_CHAR(MYDATE, 'DD/MM/YYYY hh24:mi:ss'),cast(MYDATE as timestamp) from MYTABLE where ID=1
gives this
Code: Select all
1 00/01/3948 00:00:00 31/12/47 00:00:00,000000000
Like I said i'm using RCP so i don't declare any datatypes
Posted: Tue Dec 24, 2019 5:50 am
by chulett
So... how is your MYDATE column defined in Oracle? It doesn't seem to be a DATE otherwise your TO_CHAR would work a bit more better. And the TO_TIMESTAMP function might be a better choice than CAST for the second conversion. Hard to know from this end, not knowing the source datatype or what the contents look like before the conversion attempt.
Please clarify your "gives this" example - gives this where? That's inside the job or from a tool like sqlplus or Toad or... ?
Posted: Wed Dec 25, 2019 11:41 pm
by thurmy34
Hi
The mydate column is a date.
The select was launched with sqldeveloper.
Thanks
Posted: Thu Dec 26, 2019 10:03 am
by chulett
Sorry to keep harping on this but when you say it is a date do you mean
DATE as in the datatype? Because your extracted date isn't valid and trying to convert that to a DATE fails as expected:
Code: Select all
select to_date('00/01/3948', 'mm/dd/yyyy') from dual;
ORA-01843: not a valid month
Meaning it should not be possible to store the value you extracted in a DATE field. What do you see when you simply SELECT MYDATE
without the TO_CHAR function? Would still suggest you open a support case if you haven't done so already, to see if this is some kind of odd Connector/RCP bug.
Posted: Thu Dec 26, 2019 10:04 am
by chulett
Sorry to keep harping on this but when you say it is a date do you mean
DATE as in the datatype? Because your extracted date isn't valid and trying to convert that to a DATE fails as expected:
Code: Select all
select to_date('00/01/3948', 'mm/dd/yyyy') from dual;
ORA-01843: not a valid month
Meaning it should not be possible to store the value you extracted in a DATE field. What do you see when you simply SELECT MYDATE
without the TO_CHAR function? Would still suggest you open a support case if you haven't done so already, to see if this is some kind of odd Connector/RCP bug.