We have extended our Early Bird Tickets for Inspire 2023! Discounted pricing goes until February 24th. Save your spot!

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

How To: How Does Alteryx Handle Oracle Dates

MichaelAd
Alteryx
Alteryx
Created

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

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


2019-07-03_16-57-19.png

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

Comments
kbenson
5 - Atom

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.