One Star

Date Format with Talend

I use an Oracle Input Component and the table A associated has got a column with a Date format.
I want to dump the data of this table A in an other Oracle table B which contains exactly the same columns. I have just drawn a "main row" between the two Oracle components (input and output tables).
The problem is that in the table A, the dates have got a format like 'dd/mm/yy hh:mi:ss AM', but after the dump, in the table B, the dates have got the format 'dd/mm/yy'.
How can I keep the initial format of my dates ?
16 REPLIES
One Star

Re: Date Format with Talend

It is a known bug (948)
Best Regards
Employee

Re: Date Format with Talend

pico, 948 is Java related and 566 makes me think that missjustme is working with Perl.
One Star

Re: Date Format with Talend

Hi.
With Oracle I use a tPerl component as first step in my jobs to set the Oracle date format.
$ENV{NLS_DATE_FORMAT} = 'YYYY-MM-DD HH24:MI:SS';

This solves most of my date time format problems.
One Star

Re: Date Format with Talend

pico, 948 is Java related and 566 makes me think that missjustme is working with Perl.

I am working with Java... But I thougt that even with Java, expressions (filter, ...) might be written with Perl... Is that right ?
Employee

Re: Date Format with Talend

pico, 948 is Java related and 566 makes me think that missjustme is working with Perl.

I am working with Java... But I thougt that even with Java, expressions (filter, ...) might be written with Perl... Is that right ?
No, that's wrong. If you work with Java, you have to use Java expressions and methods.
One Star

Re: Date Format with Talend

I use an Oracle Input Component and the table A associated has got a column with a Date format.
I want to dump the data of this table A in an other Oracle table B which contains exactly the same columns. I have just drawn a "main row" between the two Oracle components (input and output tables).
The problem is that in the table A, the dates have got a format like 'dd/mm/yy hh:mi:ss AM', but after the dump, in the table B, the dates have got the format 'dd/mm/yy'.
How can I keep the initial format of my dates ?

Thank you for your answer...
So how can I solve my problem with java ? :-(
Employee

Re: Date Format with Talend

While waiting for v2.01, here is a workaround :
New Routine
//template routine Java
package routines;
import java.text.SimpleDateFormat;
public class MyRoutine {
public static java.util.Date convertString(String s, String pattern) {
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
try {
return sdf.parse(s);
} catch (Exception pe) {
pe.printStackTrace();
return null;
}
}
}

In tOracleInput, replace type from Date to String (only in the tOracleInput)
add a Mapper between tOracleInput/tOracleOuput and in fill the date output with the following line
MyRoutine.convertString(row1.dateField, "yyyy-MM-dd hh:mm:ss.S")

Hope it will works.
Best Regards,
Mike
One Star

Re: Date Format with Talend

While waiting for v2.01, here is a workaround :
New Routine
//template routine Java
package routines;
import java.text.SimpleDateFormat;
public class MyRoutine {
public static java.util.Date convertString(String s, String pattern) {
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
try {
return sdf.parse(s);
} catch (Exception pe) {
pe.printStackTrace();
return null;
}
}
}

In tOracleInput, replace type from Date to String (only in the tOracleInput)
add a Mapper between tOracleInput/tOracleOuput and in fill the date output with the following line
MyRoutine.convertString(row1.dateField, "yyyy-MM-dd hh:mm:ss.S")

Hope it will works.
Best Regards,
Mike

I don't understand but it doesn't work...
When I use a tLogRow component, I can see that the date is complete :
-> I obtain this format : Tue Mar 27 11:37:52 CEST 2007
But then, when I try to substitute the tLogRow component by a tOracleOutput component, the date in my out table has got the format 27/03/07 !
Maybe the bug happens when the data are inserted in the database...
An idea ?
Employee

Re: Date Format with Talend

Whatis your Date format in Oracle table B ?
One Star

Re: Date Format with Talend

Whatis your Date format in Oracle table B ?

format of my date in table A : 'dd/mm/yy hh:mi:ss AM'
format of my date in table B : 'dd/mm/yy'

Do you think there is a solution that we can find quickly without waiting the next version of Talend ?
I am sick because except this problem of date and the null numeric value which becomes "0" in the out table, all I want to do seems to run correctly... But I can't override these problems, and if I don't succeed to solve them quickly, I will have to not use talend for my project... :-(
I feel like these problems happen when the data are converted just before the insertion in the table B...
Employee

Re: Date Format with Talend

Hello,
If your table B format is 'dd/mm/yy' we can't put anything else than this format in the field.
You need to change the table format...
One Star

Re: Date Format with Talend

Hello,
If your table B format is 'dd/mm/yy' we can't put anything else than this format in the field.
You need to change the table format...

Sorry... It is not what I wanted to say...
I wanted to say that the date I obtained in the table B has got the format 'dd/mm/yy' after the extraction...
But the data type of this field is "DATE" ! And in the table A, the field I want to dump has got the data type "DATE" too !

NB : with a simple request INSERT, I can put a date with the format "dd/mm/yy hh:mi:ss AM" in this field...

Re: Date Format with Talend

Hello,
If your table B format is 'dd/mm/yy' we can't put anything else than this format in the field.
You need to change the table format...

Sorry... It is not what I wanted to say...
I wanted to say that the date I obtained in the table B has got the format 'dd/mm/yy' after the extraction...
But the data type of this field is "DATE" ! And in the table A, the field I want to dump has got the data type "DATE" too !

NB : with a simple request INSERT, I can put a date with the format "dd/mm/yy hh:mi:ss AM" in this field...
Hi missjustme,
I had the same problem with you, and now with the routine above it works fine.
I load data from an tFileInputDelimited to a table in MySql. My field which contains some dates in my file is defined in string.
I use a tMap in order to call the routine, and I sent the pattern of my tInputFileDelimited Date ("MM/dd/yyyy HH:mm:ss"). For the field Date, in output of my Tmap, I defined the pattern that is accepted by my DB ("yyyy-MM-dd HH:mm:ss").
When I use a tLogRow, I see the same format that you ("Tue Mar 27 11:37:52 CEST 2007") but after when it writes the row in my Database, I had the that I defined ("yyyy-MM-dd HH:mm:ss").
Best Regards,
One Star

Re: Date Format with Talend

Hello,
If your table B format is 'dd/mm/yy' we can't put anything else than this format in the field.
You need to change the table format...

Sorry... It is not what I wanted to say...
I wanted to say that the date I obtained in the table B has got the format 'dd/mm/yy' after the extraction...
But the data type of this field is "DATE" ! And in the table A, the field I want to dump has got the data type "DATE" too !

NB : with a simple request INSERT, I can put a date with the format "dd/mm/yy hh:mi:ss AM" in this field...
Hi missjustme,
I had the same problem with you, and now with the routine above it works fine.
I load data from an tFileInputDelimited to a table in MySql. My field which contains some dates in my file is defined in string.
I use a tMap in order to call the routine, and I sent the pattern of my tInputFileDelimited Date ("MM/dd/yyyy HH:mm:ss"). For the field Date, in output of my Tmap, I defined the pattern that is accepted by my DB ("yyyy-MM-dd HH:mm:ss").
When I use a tLogRow, I see the same format that you ("Tue Mar 27 11:37:52 CEST 2007") but after when it writes the row in my Database, I had the that I defined ("yyyy-MM-dd HH:mm:ss").
Best Regards,
Thanks for your answer. I have some questions.

How do you define the pattern that is accepted by your DB ("yyyy-MM-dd HH:mm:ss") in output of your Tmap ? Do you use a particular function that you have implemented ?
I dont understand when you say :
I sent the pattern of my tInputFileDelimited Date ("MM/dd/yyyy HH:mm:ss")

Re: Date Format with Talend

Thanks for your answer. I have some questions.

How do you define the pattern that is accepted by your DB ("yyyy-MM-dd HH:mm:ss") in output of your Tmap ? Do you use a particular function that you have implemented ?
I dont understand when you say :
I sent the pattern of my tInputFileDelimited Date ("MM/dd/yyyy HH:mm:ss")


Hi,
This is an example of my date, that is defined in string "11/14/2006 14:05:34", its pattern is "MM/dd/yyyy HH:mm:ss". I want to transform this string field in a date field with the pattern corresponding to the pattern o MySql that is "yyyy-MM-dd HH:mm:ss".
Then I call the routine in order to transform it.
I do screen capture in order to see where I defined the pattern to my ouput field
One Star

Re: Date Format with Talend

I tried, but it doesn't run... Smiley Sad

Maybe it doesn't run with oracle, or I don't use the right date pattern for Oracle because I don't know it... I tried with this pattern : "DD/MM/YY HH:MISmiley FrustratedS AM", but the dates inserted in the table still had the format "DD/MM/YY"...