Oracle DB - write datetime

[AlessandroCic]

Hi
In an Oracle DB (version 8.1.7.1.0) I simply would like to create a mirror copy to an existing table.
One of its fields is a DateTime and when I transfer the data I have the error:

Exception in component tOracleOutput_1
java.lang.ClassCastException: java.sql.Timestamp cannot be cast to java.sql.Date
	at oracle.jdbc.driver.OraclePreparedStatement.setObject

I tred to setting the model data for the field in "yyyy-MM-dd hh:mm:ss" and in an expression I converted the original date in a text and and then I re-format it in a date

String sDate =
TalendDate.getPartOfDate("YEAR",row1.DateInput) + "-" +
TalendDate.getPartOfDate("MONTH",row1.DateInput) + "-" +
TalendDate.getPartOfDate("DAY_OF_MONTH",row1.DateInput) + " " +
TalendDate.getPartOfDate("HOUR_OF_DAY",row1.DateInput) + ":" + 
TalendDate.getPartOfDate("MINUTE",row1.DateInput)  + ":" +
TalendDate.getPartOfDate("SECOND",row1.DateInput);
Var.dDateOut = TalendDate.parseDate("yyyy-mm-dd HH:mm:ss", sDate);

but I have the same error.
If I print the values of my variable I have:

DateInput	Fri Nov 11 07:23:06 CET 2016
DateInString	2016-10-11 7:23:6
DateOut	Tue Oct 11 07:23:06 CEST 2016

Where the date It's appear correct in the format but with a different month but not valid to insert in the table.
Can you help me?
Regards
Alessandro

[vapukov]

You can parse and format this date direct, by use "EEE MMM dd HH:mm:ss Z yyyy" date pattern 

[AlessandroCic]

Hi Vapukov, hi forum
Can you write an example of use of your format "EEE MMM dd HH:mm:ss Z yyyy" ?
If I use it in the function

System.out.println(TalendDate.parseDate("EEE MMM dd HH:mm:ss Z yyyy", "" + row1.MyDate));

it not works

Exception in component tMap_1
java.lang.RuntimeException: java.text.ParseException: Unparseable date: "Mon Nov 21 07:23:17 CET 2016"

However my purpose is to copy a date time field from a table to another without transformations.
If I wish to do the same thing in SQL I use the command:

TO_DATE('2016-11-21 08:08:08', 'YYYY-MM-DD HH24:MI:SS') 

and I'm looking for a correct expression to write a date in an Oracle DB table
Regards

[vapukov]

You have error in 

System.out.println(TalendDate.parseDate("EEE MMM dd HH:mm:ss Z yyyy", ___"DELETE THIS" +____ row1.MyDate));

 
 

[AlessandroCic]

Hi Vapukov
If I delete the empty string ""

TalendDate.parseDate("EEE MMM dd HH:mm:ss Z yyyy", row1.MyDate)

I have the error:
It's regular, you start from a string and you haven't the necessity to convert it.
My code convert date to string:

TalendDate.parseDate("EEE MMM dd HH:mm:ss Z yyyy", "" + row1.MyDate)

I tried to create a job like your

but I still get the error:

Inizio job New: 15:33 21/11/2016.
 connecting to socket on port 3668
 connected
Exception in component tMap_1
java.lang.RuntimeException: java.text.ParseException: Unparseable date: "Mon Nov 21 07:23:17 CET 2016"
	at routines.TalendDate.parseDate(TalendDate.java:895)
	at routines.TalendDate.parseDate(TalendDate.java:839)

In any case are you unable to write your value in an Oracle database date field?

[AlessandroCic]

Up.
Is there no one who writes or updates a date-time field in Oracle?
On sql the syntax is:

to_date('2016-11-23 10:15:31', 'YYYY-MM-DD HH24:MI:SS')

How can I white or update my date in Talend?
Regards

[JR1]

Hi
First of all, there is no "date-time field" in Oracle. There is DATE (which can also hold the time), there is TIMESTAMP and then there is TIMESTAMP WITH TIMEZONE. Are you by any chance trying to convert a TIMESTAMP WITH TIMEZONE into a DATE? Please try the following (nothing is tested):

Change your input column to type "Date" (DB type should be set to the type in your database)
Change the date format in this column (Modello data) to the source Oracle date/timestamp format - in your case it should be "EEE MMM dd HH:mm:ss Z yyyy" (assuming you are reading from a TIMESTAMP WITH TIMEZONE column).
Change your expression to "row1.MyDate".
Change the date format in this column (Modello data) to the target Oracle date/timestamp format - I believe you are trying to use "yyyy-MM-dd HH:mm:ss".
Use "Date" in the schema of the output component and the appropriate DB type (I assume it is DATE).

Please note that there are some factors playing into this, therefore, we cannot simply provide you with a perfect solution. You will have to try to understand the Java and Oracle date patterns and how Talend reads and converts dates and timestamps from/into DB columns. You will have to play around with this.

Another way could be to create the query manually in the "Query" field of tOracleInput using the TO_DATE() function returning the correct format for the target. Then you can simply pass this on as a string.

Regards,
Joachim

[mboss1649680202]

Hi,

In oracleOutput component -> advanced settings

uncheck checkbox against "Use Timestamp for Date types"