topic Re: Date Format with Talend in Talend Studio

Date Format with Talend

Anonymous — Sat, 16 Nov 2024 14:37:00 GMT

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 ?

Re: Date Format with Talend

Anonymous — Tue, 17 Apr 2007 18:10:58 GMT

It is a known bug (948)
Best Regards

Re: Date Format with Talend

Anonymous — Tue, 17 Apr 2007 18:34:10 GMT

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

Re: Date Format with Talend

Anonymous — Wed, 18 Apr 2007 07:57:50 GMT

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.

Re: Date Format with Talend

Anonymous — Wed, 18 Apr 2007 08:26:55 GMT

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 ?

Re: Date Format with Talend

Anonymous — Wed, 18 Apr 2007 09:20:49 GMT

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.

Re: Date Format with Talend

Anonymous — Wed, 18 Apr 2007 09:24:04 GMT

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 ? 😞

Re: Date Format with Talend

Anonymous — Wed, 18 Apr 2007 18:13:33 GMT

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

Re: Date Format with Talend

Anonymous — Thu, 19 Apr 2007 15:28:18 GMT

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 ?

Re: Date Format with Talend

Anonymous — Thu, 19 Apr 2007 23:53:48 GMT

Whatis your Date format in Oracle table B ?

Re: Date Format with Talend

Anonymous — Fri, 20 Apr 2007 08:29:51 GMT

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

Re: Date Format with Talend

Anonymous — Fri, 20 Apr 2007 10:44:24 GMT

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

Re: Date Format with Talend

Anonymous — Fri, 20 Apr 2007 12:23:08 GMT

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

Re: Date Format with Talend

Anonymous — Mon, 23 Apr 2007 09:23:46 GMT

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,

Re: Date Format with Talend

Anonymous — Mon, 23 Apr 2007 10:22:35 GMT

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

I sent the pattern of my tInputFileDelimited Date ("MM/dd/yyyy HH:mm:ss")

Re: Date Format with Talend

Anonymous — Mon, 23 Apr 2007 11:20:53 GMT

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

Re: Date Format with Talend

Anonymous — Mon, 23 Apr 2007 12:42:46 GMT

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

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:MI S AM", but the dates inserted in the table still had the format "DD/MM/YY"...