One Star

[resolved] Date time format

One of my jobs has a tMap component. Within that component I have a Var (string) which has following expression:
TalendDate.getDate("DD-MM-CCYY") 
Result of above expression inserted as a string into field within Oracle table via tOracleOutput component and that field's data type is DATE. It works fine as is.
I changed above expression to include time: TalendDate.getDate("DD-MM-CCYY HH:mm:ss"). Talend compiles it correctly and job runs without any errors. However, record with newly formatted date/time is not inserted into table via tOracleOutput component.
Any suggestions on what format to use so that date would include time and record with that date/time would be inserted into table?
Thank you,
Victor
1 ACCEPTED SOLUTION

Accepted Solutions
One Star

Re: [resolved] Date time format

As per your suggestion, I changed data type to DATE and used TalendDate.getCurrentDate(). Also, on insert into table I selected format "dd-MM-yyyy'T'HH:mm:ss" from available Date Patterns for DATE column. It works now as it should.
Thank you for your help.
Victor
6 REPLIES
Four Stars

Re: [resolved] Date time format

Hi Victor,
What is the date format in tOracleOutput component? You need to use format dd-MM-yyyy'T'HH:mm:ss.
Vaibhav
One Star

Re: [resolved] Date time format

Vaibhav,
Thank you for quick response.
ActivityDate field in tOracleOutput component is VARCHAR2. I tried to use format dd-MM-yyyy'T'HH:mm:ss in tMap component and getting the same result: job runs without errors and records not inserted into Oracle's table.
Victor
Four Stars

Re: [resolved] Date time format

Connect reject link from database, this way you will be sure whether the records are rejected from database or not. If yes, then there is an issue with database table data type.. it may be datetime and not date.
Vaibhav
Seventeen Stars

Re: [resolved] Date time format

There are perhaps some misunderstandings:
If the Oracle data type is VARCHAR2 the only problem what potential prevents the writing is a to large length of the value.
If the Oracle data type is DATE, than you should not try to write a String typed value, generating a date can be done with TalendDate.getCurrentDate() which returns a real Date object - exactly what Oracle expects here.
A DATE column in Oracle (or any other database) does not have a format or pattern. Formats/patterns are only a way to describe how to convert a textual representation of a DATE should be converted into the binary representation (the DATE !) and vis versa.
The statement "...tOracleOutput.... You need to use format dd-MM-yyyy'T'HH:mm:ss" is not correct for DATE or TIMESTAMP typed columns because these columns accepts only real Date or Timestamp objects as values and nothing else. The pattern of the schema Date columns are only be used for additional log outputs but never for the actual work of writing a Date value into the database.
Conclusion: If you want to write into a DATE typed database field -> use a Java Date typed value.
To be sure you get all error messages, switch on the option Die on error and switch off the Batch mode (advanced settings).
One Star

Re: [resolved] Date time format

Thank you for response. I turned Die on Error and got:
"Exception in component tOracleOutput_1
java.sql.BatchUpdateException: ORA-01843: not a valid month"
Victor
One Star

Re: [resolved] Date time format

As per your suggestion, I changed data type to DATE and used TalendDate.getCurrentDate(). Also, on insert into table I selected format "dd-MM-yyyy'T'HH:mm:ss" from available Date Patterns for DATE column. It works now as it should.
Thank you for your help.
Victor