topic Re: Inserting date with time component into a tOracleOutput in Talend Studio

Inserting date with time component into a tOracleOutput

Anonymous — Tue, 15 Mar 2016 15:53:08 GMT

Hi guys,
Using tMap, I try to insert some data into a tOracleOutput.
I need to map one of date columns on the tOracleOutput as follow :

TalendDate.parseDate("dd/MM/yyyy HH:mm:ss",TalendDate.formatDate("dd/MM/yyyy",TalendDate.getCurrentDate()).concat(" ").concat(row3.HOUR).concat(":00"),true)

where row3.HOUR is a string column from an input and represents hours (e.g "08:30").
The purpose of this mapping is to concatenate the current date (eg. 15/03/2016) with the hour component from the input flow row3 (eg. "08:30") and then insert this new date (15/03/2016 08:30) into the database table.
Unfortunately, date recorded in the database table is truncated : 15/03/2016 00:00:00 while expected is 15/03/2016 08:30:00.
The same mapping sent to a tLogRow component show the correct output.

I'll be thankfull for any help.

Rado.

Re: Inserting date with time component into a tOracleOutput

Anonymous — Wed, 16 Mar 2016 16:59:35 GMT

I answer to myself in case of someone is facing the same issue in the future.

TalendDate routines manipulate java.util.Date objects.
But when it's time to persist the object into a database table, a java.sql.Date is created from the java.util.Date object, like this :

new java.sql.Date(out1.DATE_DOC.getTime())

where out1.DATE_DOC is the initial Date object - this object is a full precision date.

The problem is that java.sql.Date objects don't have time parts.
So when a java.sql.Date object is created from a java.sql.Date object (as shown on pervious snippet), the date is truncated and precision is lost.
Conclusion :
- I cannot persist a full precision date (date and time) into a DATE column of a database table without loosing the time precision.
- To achieve my goal, I created a VARCHAR column in my table for the date and use database side trigger to compute the date.