How to insert data into Oracle (date column) with format 'YYYY-Mon-DD' ?

Question

Hi,

1. My original input data is a string with format 'YYYY-MM-DD' (Example: 2021-03-18).

2. Use 'Formula' component to convert the data into a string with format 'YYYY-Mon-DD' (Example: 2021-Mar-18).

3. Use 'Select' component to convert 'YYYY-Mon-DD' data from STRING data type into DATE data type and plan to insert into a Oracle date column, but failed with error message:

Select (7) BIRTH_DATE: 18-Mar-202 is not a valid Date

Is there any solution to resolve the issue?

Thanks,

Artie

apathetichell · Answer

All good! and hoping that this works out. Double checked btw and there are no set field lengths for date fields in-db. It's DB dependent. Remember to use quotes not brackets for your fieldnames...

ARTIEWANG · Answer

I think you are right.

I was using components under In/Out category.

In order to read/write from/to Oracle, I think I should use components under In-Database category. Let me re-write the ETL and get back to you.

Thanks again for clarify my confusions.

apathetichell · Answer

I believe Oracle also recognizes dates as YYYY-mm-dd but hypothetically if you really need the date in Oracle to be YYYY-bb-mm you can datastream in (convert to in-db) - then use formula in-db. This will execute in Oracle's native function syntax and I assume would work with Oracle native data type formats. Then you would Write In-DB. This would allow you to write a database to Oracle which I assume is your end result? You would have to do all manipulation to place your Date in that syntax In-DB and on Oracle.