Alteryx Auto Insights Knowledge Base

Definitive answers from Auto Insights experts

Auto Insights: One of The Consequences of Wrong Date Format

rkong
Alteryx
Alteryx
Created

Auto Insight's recommended date format is YYYY-MM-DD. However, it 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 is able to 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. This article shows one of the consequences if there is only data for four days: 1st Sept 2021, 1st Oct 2021, 1st Nov 2021, and 1st Dec 2021 when the date format is recommended and wrong format. 
 

Prerequisites

 

  • Auto Insights User Portal
  • Auto Insights Admin Portal
  • Data source from cloud MS SQL


Example


There are 1527 rows of data for four days: 1st Sept 2021, 1st Oct 2021, 1st Nov 2021, and 1st Dec 2021.

The reference input uses recommended date format YYYY-MM-DD.

image.png

The problematic input uses UK/AU date format DD-MM-YYYY (MS SQL date format 105).

image.png


Common Issues


Here is the correct result from recommended date format. It detects the data is for four months, so it uses a quarter as the unit of time.

image.png

Here is the result from the wrong date format. It considers the data is for four days from Jan 9th, 2021, to Jan 12th, 2021, so it uses a day as the unit of time.

image.png

Therefore, it is strongly recommended to use the correct date format YYYY-MM-DD.

Additional Resources