topic Re: Oracle DB - write datetime in Talend Studio

Oracle DB - write datetime

AlessandroCic — Fri, 11 Nov 2016 12:22:32 GMT

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

Re: Oracle DB - write datetime

vapukov — Fri, 11 Nov 2016 13:26:56 GMT

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

Re: Oracle DB - write datetime

AlessandroCic — Mon, 21 Nov 2016 09:04:59 GMT

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

Re: Oracle DB - write datetime

vapukov — Mon, 21 Nov 2016 09:16:01 GMT

You have error in

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

Re: Oracle DB - write datetime

AlessandroCic — Mon, 21 Nov 2016 14:45:05 GMT

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?

Re: Oracle DB - write datetime

AlessandroCic — Wed, 23 Nov 2016 09:15:12 GMT

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

Re: Oracle DB - write datetime

JR1 — Wed, 23 Nov 2016 11:11:04 GMT

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

Re: Oracle DB - write datetime

mboss1649680202 — Wed, 13 Apr 2022 11:03:41 GMT

Hi,

In oracleOutput component -> advanced settings

uncheck checkbox against "Use Timestamp for Date types"