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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Inconsistent Date format (string)

Meteor

I have a field that contains date values, currently a string field, that has multiple formats that i need to convert to an actual date field.  The current values are "yyyy-mm-dd" and "mm/dd/yyy".  I have tried the DateTime parse tool but with multiple formats this isnt successful.  Anyone run across this?

 

ACE Emeritus
ACE Emeritus

I would recommend just something like....

IIF(Contains([f1],'-'),[f1],
    DateTimeParse([f1],"%m/$d/%Y"))

 

yep - was one of the reasons I build a custom tool: https://github.com/jdunkerley/AlteryxAddIns/releases/tag/v0.3

 

Easiest work around is a formula tool:

REGEX_Replace([Field1],"(\d\d)/(\d\d)/(\d{4})","$3-$1-$2")

This will convert mm/dd/yyyy to yyyy-mm-dd

 

There is no need to parse yyyy-mm-dd as Alteryx knows it as a date anyway

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@jdunkerley79

beat me to the punch, but here is what I did differently:

 

IF
Contains([date],"-") THEN date
ELSEIF
Contains([date],"/") THEN Regex_Replace([date],"(\d{1,2})(/)(\d{1,2})(/)(\d{4})","$5-$1-$3")
ELSE
Null()
ENDIF

I setup a conditional set of logic to handle and look at the data (this includes a default of Null).  You could change the default date if you want and if you find that you have another format of data, you can create another ELSEIF statement.

 

I didn't know that I could not use the (/) group on the delimiter.  Knowing that, I might use a \W instead.  It is a non-word character to look for any delimiter.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Meteor

Thanks Marquee, this gave me exaclty what i needed!

ACE Emeritus
ACE Emeritus

Just out of curiosity for you Regex gurus, the following will handle single-digit month and days...

IIF(Contains([f1],'-'),[f1],
    REGEX_Replace([f1],"(\d{1,2})/(\d{1,2})/(\d{4})","$3") + '-' + 
    Right('0' + REGEX_Replace([f1],"(\d{1,2})/(\d{1,2})/(\d{4})","$1"),2) + '-' + 
    Right('0' + REGEX_Replace([f1],"(\d{1,2})/(\d{1,2})/(\d{4})","$2"),2))

But it's pretty ugly... is there a way to modify your original "$3-$1-$2" format to include the '0' padding "Right" approach within Regex output syntax?

 

Right(REGEX_Replace(
   "0" + Replace([Field1],"/","/0"),
   "0?(\d{2})/0?(\d{2})/0?(\d{4})","$3-$1-$2"),10)

is my normal trick

 

Add 0's everywhere then regex it out

ACE Emeritus
ACE Emeritus

That's cool, James.  In the meantime this is what I came up with:

Replace(REGEX_Replace([Field1],"(\d{1,2})/(\d{1,2})/(\d{4})","$3-0$1-0$2"),"-00","-0")

 EDIT: OK, all done simplifying...

Sr. Community Content Manager
Sr. Community Content Manager

For those who'd prefer to avoid regex at all costs, the Cleanse tool can handle this conversion (with a select tool afterward to convert from string to date).

Untitled.png

Asteroid

Hi Neil,

 

That's a very useful macro.

I tried modifying it to handle additional date formats (put the formats on the 'Input Formats' List Box and added formulas that handle them) but it does not work. Could you tell me what do I need to do to expand the macro's functionality?

 

Thanks

Michal

Labels