topic Re: [resolved] MySQL Default Date 0000-00-00 output to Oracle as Dec 13 2003 in Talend Studio

[resolved] MySQL Default Date 0000-00-00 output to Oracle as Dec 13 2003

_AnonymousUser — Sat, 16 Nov 2024 13:38:25 GMT

I am wondering if anyone of you know why when using Talend to output the data from MySQL to Oracle when MySQL date is 0000-00-00, it imported into Oracle as Dec 13, 2003.
Any help is appreciated! Thanks!

Re: [resolved] MySQL Default Date 0000-00-00 output to Oracle as Dec 13 2003

Anonymous — Mon, 21 Dec 2009 05:45:15 GMT

Hello
Please see the related 1041.
You can set dates prior to January 1st, 1970 or null if the date is a 'zero' date.
Best regards

shong

Re: [resolved] MySQL Default Date 0000-00-00 output to Oracle as Dec 13 2003

_AnonymousUser — Mon, 21 Dec 2009 20:09:15 GMT

Hi Shong,
Thanks for your help! I looked into the Forum topic 1041 and entered the expression provided in the forum in my tmap as follows:
row1.story_date.getTime()<0?null:row1.story_date
However, I encountered the following error:
"Exception in thread "main" java.lang.Error: Unresolved compilation problem:"
From my tmap, row1.story_date is the date which contains 0000-00-00 00:00:00 data.
Can you please give me additional guidelines as this is the first time I work on Talend.
Thanks again!!

Re: [resolved] MySQL Default Date 0000-00-00 output to Oracle as Dec 13 2003

Anonymous — Tue, 22 Dec 2009 01:52:25 GMT

Hello

"Exception in thread "main" java.lang.Error: Unresolved compilation problem:"

Go to the code tab and see which line occurs the compilation error.
What's data type of story_date in your schema? It should be Date type here.
Best regards

shong

Re: [resolved] MySQL Default Date 0000-00-00 output to Oracle as Dec 13 2003

_AnonymousUser — Tue, 22 Dec 2009 18:00:44 GMT

Hi Shong,
I am not sure where I can locate the code tab? I used the "Test" button in the expression builder and encountered the error.
In addition, can I ask if I want to insert date: 01-Jan-1970 instead of null value for date 0000-00-00, will the following code works:
row1.story_date.getTime()<0?1970-01-01:row1.story_date
Thanks again! Have a nice day!

Re: [resolved] MySQL Default Date 0000-00-00 output to Oracle as Dec 13 2003

Anonymous — Wed, 23 Dec 2009 02:27:35 GMT

Hello

I am not sure where I can locate the code tab?I used the "Test" button in the expression builder and encountered the error.

I mean the generated code tab. (see my screenshot)

can I ask if I want to insert date: 01-Jan-1970 instead of null value for date 0000-00-00,

Yes, if the data type of column is Date, set the expression as:

row1.story_date.getTime()<0?TalendDate.parseDate("yyyy-MM-dd","1970-01-01"):row1.story_date

Best regards

shong

Re: [resolved] MySQL Default Date 0000-00-00 output to Oracle as Dec 13 2003

_AnonymousUser — Wed, 23 Dec 2009 18:47:51 GMT

Hi Shong,
Thanks again for your helping me!!
For the CODE tab, I clicked on it and didn't seem to have compilation error. When I executed the job, the date is transformed correctly to null into Oracle.
Question
======
What is the use of the "Test" button in expression builder? Because everytime I used this "Test" button in the expression builder, I encountered compilation error? How should I compile the job and make sure the code I used is correct?
Thanks again!!

Re: [resolved] MySQL Default Date 0000-00-00 output to Oracle as Dec 13 2003

Anonymous — Thu, 24 Dec 2009 03:07:12 GMT

Hello

What is the use of the "Test" button in expression builder? Because everytime I used this "Test" button in the expression builder, I encountered compilation error? How should I compile the job and make sure the code I used is correct?

Go to the 'code' tab and see if there are some compilation error, if not the code is correct. About 'Test' button, it check the result of the expresion you set.
Best regards
shong

Re: [resolved] MySQL Default Date 0000-00-00 output to Oracle as Dec 13 2003

Anonymous — Sun, 27 Dec 2009 10:32:11 GMT

Hi Shong,
Thanks for your detailed explanations!

Re: [resolved] MySQL Default Date 0000-00-00 output to Oracle as Dec 13 2003

Anonymous — Thu, 13 Dec 2018 09:46:59 GMT

Hello. I have same problem with dates from AS400.. I see no screenshoots in your post how to do this in t-map..Thx