How To: How Does Alteryx Handle Oracle Dates

[MichaelAd]

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

• Product - Alteryx (Designer or Server)
• Product - Third Party (Oracle Drive and connection to Oracle database).
  • Supported versions.

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:

• Database level: SELECT * FROM nls_database_parameters WHERE parameter = 'NLS_DATE_FORMAT';
• Instance level:SELECT * FROM nls_instance_parameters WHERE parameter = 'NLS_DATE_FORMAT';
• Session level:SELECT * FROM nls_session_parameters WHERE parameter = 'NLS_DATE_FORMAT';

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

Spoiler (Highlight to read)

When connecting to an Oracle DB filtering (in-DB) on date as displayed in the long format in Designer you might encounter the following error.

Error: ORA-01861: literal does not match format string

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 

This error message comes from the fact that internally, as explained above Oracle uses thedefault date format DD-MON-YY to store the date.
To solve the issue use in the Filter tool the Oracle default data format.

When connecting to an Oracle DB filtering (in-DB) on date as displayed in the long format in Designer you might encounter the following error.Error: ORA-01861: literal does not match format stringBrowse 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

• Oracle Database Online Documentation: Setting up a Globalization Support Environment - NLS_DATE_FORM...

• Oracle Database Online Documentation: ALTER SESSION

[kbenson]

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.