topic Re: Talend reformating database date fields in Talend Studio

Talend reformating database date fields

talendtester — Sat, 16 Nov 2024 13:46:05 GMT

When I exported the results of a SQL query (against an Oracle database) to a flat file, I noticed Talend is changing the format for date fields.
SqlPlus output format (Format I would like the output to be):
20-MAR-09 06.20.00.000000 PM
Talend:
Fri Mar 20 18:20:00 MST 2009
I am using a tMap object and auto mapping the fields.
I tried the following for to go from a date to a date:
Attempt 1:
TalendDate.setDate(row2.CREATE_DATE, 01, "dd-MMM-yy HH:mm:ss.SSSS")
Attempt 2:
TalendDate.formatDate("dd-MMM-yy HH:mm:ss.SSSS", row2.CREATE_DATE)
Attempt 3:
TalendDate.formatDate("day MMM dd HH:mm:ss yyyy", TalendDate.parseDate("dd-MMM-yy HH.mm.ss.SSSS PM",row2.CREATE_DATE));

Error:
Exception in thread "main" java.lang.Error: Unresolved compilation problem:
The method parseDate(String, String) in the type TalendDate is not applicable for the arguments (String, Date)
at myjob.tOracleInput_4Process(myjob.java:8776)
at myjob.runJobInTOS(myjob.java:11133)
at myjob.main(myjob.java:11049)
Job myjob ended at 16:02 14/09/2009.

Re: Talend reformating database date fields

Anonymous — Tue, 15 Sep 2009 03:13:32 GMT

Hello
The method parseDate(String, String) parse a string a Date.
The method formatDate(String, Date) format a Date to a string.
In you case, row2.CREATE_DATE is a Date, and you want to format it to string with a pattern.
So, you could use the following method to format a Date a string:
TalendDate.formatDate("dd-MMM-yy HH:mm:ss.SSS aa", row2.CREATE_DATE)
Best regards
shong

Re: Talend reformating database date fields

talendtester — Tue, 15 Sep 2009 19:58:02 GMT

The following works when I change the Output type to String and leave the Input type to Date
TalendDate.formatDate("dd-MMM-yy HH.mm.ss.SSSSSS aa",row2.CREATE_DATE);
Why would Talend auto mapping change the formatting of date fields?

Re: Talend reformating database date fields

Anonymous — Wed, 16 Sep 2009 02:40:26 GMT

Hello

Why would Talend auto mapping change the formatting of date fields?

The input type is a Date, so Talend print it with the locale default format. If you want to see it in one pattern, you need format it as you do above.
Best regards
shong