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

[_AnonymousUser]

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!

[Anonymous]

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

[_AnonymousUser]

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!!

[Anonymous]

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

[_AnonymousUser]

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!

[Anonymous]

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

[_AnonymousUser]

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!!

[Anonymous]

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

[Anonymous]

Hi Shong,
Thanks for your detailed explanations!

[Anonymous]

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