community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

Best way to fix inconsistent format within one column.

Highlighted
Meteoroid

Hi All,

 

I have one DateTime column with very inconsistent formats.

 

[Column]

11/23/2011  3:15:09

2014-07-23  17:44:54

1/30/2017   6:21:04

 

I have been using two rounds of Regex to make the format consistent like yyyy-MM-dd hh:mm:ss and managed to "fix" it, including the date and time with only 1 digit (by appending a leading 0) etc.

 

Being a newbie in Alteryx, I was wondering whether there is a more elegant/efficient/cleaner way of achieving the same output?

 

Thanks,

Viv

 

 

 

I suggest the BB Date macro: https://community.alteryx.com/t5/Engine-Works-Blog/Introducing-BB-Date-Date-Parsing-for-the-Rest-of-...

 

Another option is the OmniBus tools I maintain. They have a .NET based DateTime parser which will guess format for you. You would need to tell culture to identify M/D/Y for D/M/Y

 

Otherwise, you could construct some complex formula to identify format and DateTime parse to Alteryx format. I would do a sequence of steps in a formula tool with each one like:

IIF(REGEX_Match([Column],"\d+/\d+/\d+\W+\d+:\d+:\d+"),
DateTimeParse([Column],"%m/%d/%Y %H:%M:%S"),
[Column])
Labels