Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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 ?
Labels (2)
16 Replies
Anonymous
Not applicable
Author

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...
Anonymous
Not applicable
Author

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...
Anonymous
Not applicable
Author

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...
Anonymous
Not applicable
Author

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,
Anonymous
Not applicable
Author

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")
Anonymous
Not applicable
Author

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
Anonymous
Not applicable
Author

I tried, but it doesn't run... 0683p000009MPcz.png

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