This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
Product - Alteryx (Designer or Server)
Product - Third Party (Oracle Drive and connection to Oracle database).
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:
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.
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