How to insert data into Oracle (date column) with format 'YYYY-Mon-DD' ?
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Labels:
- Date Time
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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...
![](/skins/images/5397F93796E5FC69E1217E3516A4A874/responsive_peak/images/icon_anonymous_message.png)