Alteryx Auto Insights Knowledge Base

Definitive answers from Auto Insights experts

How to Prepare Date Format for Auto Insights

rkong
Alteryx
Alteryx
Created

How to Prepare Date Format for Auto Insights


Auto Insights uses an algorithm to detect the date format. It works well when there are many discrete date values at a daily level.

For example, Auto Insights can automatically determine whether the date format is YYYY-MM-DD or DD-MM-YYYY (UK/AU Format) or MM-DD-YYYY (US Format) given enough samples. However, when there are very few date values, Auto Insights cannot determine this and will detect it as an incorrect date format. An example of this is 2021-01-01, 2021-01-02, and 2021-01-03. Are these the first 3 days of January? Or the 1st day of Jan, Feb, and March?

It is recommended the format in the data be YYYY-MM-DD to circumvent this and ensure the highest chance of a successful data import.


Prerequisites

  • Auto Insights
  • Postgres
  • MYSQL
  • MS SQL
  • Oracle
  • Snowflake

Here is an example of main-stream date formats in MS SQL.

image.png


Procedure


Here are SQL functions to convert date format into "YYYY-MM-DD" from the different supported data sources.
 
  • Postgres
PostgreSQL uses the "yyyy-mm-dd" format for storing and inserting date values. To convert a field to date format, use this code:
 
TO_CHAR([datecolumn] :: DATE, 'YYYY-MM-DD')
TO_DATE([datecolum], 'YYYY-MM-DD')
 
  • MYSQL
MySQL retrieves and displays DATE values in '"YYYY-MM-DD" format.  To convert a field to date format, use this code:
DATE_FORMAT([datecolumn], '%Y-%m-%d')
 
  • MS SQL
SQL Server outputs date values in "yyyy-mm-dd" format.  To convert a field to date format, use this code:
convert(date, [datecolumn], 105)   --UK/AU Format DD-MM-YYYY or DD/MM/YYYY
convert(date, [datecolumn], 101)   --US Format MM-DD-YYYY or MM/DD/YYYY
 
  • Oracle
Oracle's default format for DATE is "DD-MON-YY".  To convert a field to date format, use this code:
TO_CHAR([datecolumn], 'YYYY-MM-DD')
TO_DATE([datecolumn], 'YYYY-MM-DD')
  • Snowflake
Snowflake supports a single DATE data type for storing dates (with no time elements). DATE data type accepts dates in the most common forms ( YYYY-MM-DD, DD-MON-YYYY, etc.)  To convert a field to date format, use this code:
to_char([datecolumn] :: date, 'yyyy-mm-dd')
to_varchar([datecolum], 'yyyy-mm-dd')

In summary, the best practice is to first convert the date format into 'YYYY-MM-DD'.