11-20-2019 05:47 AM - edited 07-23-2021 03:32 PM
How To: How Does Alteryx Handle Oracle Dates
The Oracle default data format is DD-MON-YY. However, additionally, different data formats can be set per session. While this might seem at first confusing it can be useful if understood properly. Alteryx Designer on the other hand usesYYYY-MM-DD HH-mm-ss as the default date format and data containing dates imported from Oracle will show up converted into that date format.
Prerequisites
Dates in Oracle and in Alteryx Designer
The Oracle default date format for input and output isDD-MON-YY e.g. 01-JAN-19. It is controlled by the value of the NLS_DATE_FORMAT parameter. In other words the default value ofNLS_DATE_FORMAT is:
DD-MON-YY
Please note that the date format can be set on database level, on instance level, and on session level.
The below SQL queries will return information on the data format being used:
To modify the current session data format use the ALTER SESSION statement. This statement stay in effect until disconnecting from the database.
For instance you can use the below to change the session date format:
ALTER SESSION SET nls_date_format = 'dd/mon/rr'
On the other hand the default data format in Alteryx is YYYY-MM-DD HH-mm-ss.
Data columns of Date data type imported from Oracle into Designer will be displayed in Designer's default format. When filtering data of data type Date in Designer it is important to use the date format that is used in the underlying Oracle database even though the date format might be different from the one displayed in Designer.
Common Issues
Browse In-DB (6) DataWrapOCI: Unable to prepare the query: "WITH "Tool1_ba3e" AS (Select * From HH_STORE_FILE), "Tool5True_799e" AS (SELECT * FROM "Tool1_ba3e" WHERE "DATE_COLUMN" = '2019-07-03 00:00:00') SELECT * FROM "Tool5True_799e" WHERE ROWNUM
Additional Resources
It is helpful to note, when using the Date Interface Tool with an Oracle database, you can use the ALTER SESSION SET nls_date_format = 'YYYY-MM-DD' statement within the "Pre SQL Statement" option of your Input Data Tool. This will allow you to filter your Oracle SQL results based on a user-specified date parameter.