topic Date format and loading into an Oracle Database. in Talend Studio

Date format and loading into an Oracle Database.

Anonymous — Sat, 16 Nov 2024 13:09:18 GMT

Hi,
I have a comma delimited file and one of the columns in the file is a date. The date format is as follows
1/5/2011 2:25:54 AM. I am trying to insert this date format into an Oracle database using the tOracleBulkExec. What is the best way of inserting this date format into a column of type date in an Oracle 10G database.
Any help/advice/suggestion is greatly appreciated.
Thank you,
Santhosh.

Re: Date format and loading into an Oracle Database.

Anonymous — Fri, 07 Jan 2011 20:47:29 GMT

you will need to define the date pattern in your input schema correctly. from your sample data it should probably be:

dd/MM/yyyy hh:mm:ss aa

Re: Date format and loading into an Oracle Database.

Anonymous — Fri, 07 Jan 2011 23:26:23 GMT

Hello John,
My date format is MM/dd/yyyy hh:mm:ss aa. I tried changing date pattern for one of the date columns to this format in the schema but it still does not work. So I am using the "into table clause" in tOracleBulkExec to insert data into the database by mentioning the date format as
column datatype format
EVENTDATE DATE 'MM/DD/YYYY HH:MI S AM'
which is in an Oracle format.
I still have not figured out how to use the date pattern format in the schema to insert date datatype data into an Oracle database. Can somebody please advice/help on how this can be done. How is the date pattern in the schema used effectively to insert a date into an Oracle database?
Thank you,
Santhosh.

Re: Date format and loading into an Oracle Database.

Anonymous — Sat, 08 Jan 2011 18:28:59 GMT

You can always have Oracle parse the date for you by passing it as a string and then using a TO_DATE in your inserts:
Under advanced settings in your ouput, replace your date column with something like this:
TO_DATE('MM/DD/YYYY HH:MI S AM',?)