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

[ARTIEWANG]

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]

Why are you doing Step 2 - convert it to YYYY-mm-DD directly and create a new column of type Date. You don't use Select in IN-DB formulas either. just write the formula...

[ARTIEWANG]

First, Thanks for your input.

Actually the 'Select' component is used for column mappings between input and output, nothing special.

If I use the 'Formula' component, create a new column named 'o_DETHDATE', set the output data type as 'DATE', then its default size=10. This size=10 is grey out and can't be changed.

        DateTimeFormat(DateTimeParse([BIRTH_DATE], '%Y-%m-%d'), '%d-%b-%Y')

However, the output is expected as '2021-Mar-18' and its size should be 11, instead of 10. That's why I got that error message as described above. The last digit of output YEAR is missing from the conversion.

[apathetichell]

that is in a standard formula tool - no? for in-db your formula should have the field as "fieldname".

Alteryx sees dates as YYYY-mm-dd. If you convert a date to a non-Alteryx friendly date type Alteryx would crash on datastream out. What is your end goal here - to get your dates in a YYYY-BBB-DD format? Alteryx does not natively recognize that as a date and would not allow you to manipulate that with datetime tools.

[apathetichell]

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.

[ARTIEWANG]

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]

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...