How to load timestamp data into Oracle table?

The data in Excel is in a timestamp format. It reads in Alteryx as DateTime. It is in the format I need. After loading in Oracle, it becomes 12 hour format with 9 extra 0s.

How to make '2020-08-28 19:06:37' loaded into Oracle as '28-AUG-20 19:06:37'?

Sample data and a screenshot of what is loaded into Oracle are attached.

Thank you for your time!

Time_data.xls

Data_timestamp_oracle.JPG

Output

Date Time

Accepted answers

atcodedog05

Hi @jovia

I might be still way from the answer but here is just a try.

Here is a workflow which converts alteryx dateformat to oracle dateformat. Can you give a try.

Output:

Hope this helps 🙂

date convert.yxmd

All comments

Qiu

@jovia
I am not expert of Oracle but maybe you need to set to column to "Date" instead of "TimeStamp" as per below.

https://apex.oracle.com/pls/apex/germancommunities/apexcommunity/tipp/6381/index-en.html

RolandSchubert

Hi @jovia ,

I think, the problem is related to column definition in Oracle. The TIMESTAMP data type has 6 digits for "fractional seconds precision" by default, so you have the option to modify the settings for the timestamp column (e.g. using 'DD-MON-YY HH:MM:SS' as a specifier) or use a DATE column instead (maximum precision is a second, that's what you need).

Hope this is helpful.

Best,

Roland

jovia

@Qiu ,

Thank you for your time. After I set these columns as date in Alteryx. They loaded as date only in Oracle. The timestamp all become 00:00:00. I will need the timestamp information for my analysis.

jovia

@RolandSchubert ,

I ran "alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS'; " in Oracle.

After these columns are loaded as date from Alteryx, they lost the time, please see the screenshot.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703