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

Alteryx designer Discussions

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

Date Conversion Frustration

Meteor

I have a dataset that has a bunch of date field in the following format "DD-MMM-YYYY"

 

It seems like I have to go thru a convoluted process to convert from a string to date. I dont understand why I can't change the type simply using the select configuration. 

 

Is anyone else annoyed with how alteryx handles dates? Seems like it should be easier. 


Thanks,

MIke  

Alteryx Certified Partner
Alteryx Certified Partner

@fitzviz,

 

Have you tried using the DATE TIME Tool?  It should greatly simplify the process for you.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Alteryx Certified Partner

Good answer from Mark, DateTime tool is fine but a bit annoying for multiple fields, multi field formula good fit for that.

 

I used this article as the base for this ;

https://community.alteryx.com/t5/Data-Preparation-Blending/Multifield-Formula-for-changing-data-from...

 

I show both approaches here for your date format with the attached workflow.

The attached workflow shows how to use the DateTime tool for each date field that you have in serial.

Alternatively, the Multi Field Formula tool can act on each of your date fields simultaneously, then convert with a select.

Metadata tab shows now date types.

 

DateTimeParseScreenShot.JPG

Meteor

Thanks for your reply the issue is that at my company the good documentation practice for dates is DD-MMM-YYYY and that is not an option that the DateTime tool recognizes as a valid incoming format type

Alteryx Certified Partner

Sorry, I'm not sure I understand.

That's what I demonstrated to you.

 

The trouble with dates is that every platform has their own versions of (sometimes case sensitive) codes for the different date parts.

 

Could you give a couple of explicit examples of the content of the string fields that you want to convert to dates ?

 

Thanks

dan

Quasar

The date time tool does let you specify exactly what format the text that you want to convert into a string is, you just may need to scroll down to the bottom of the default list and choose custom.

As for the select tool, converting a string into a date only works when the data is in the preferred format for Alteryx, which isn't the format your company is using unfortunately.

If it's just for a column or 2, then I'd recommend Date Time, with a custom format for the string coming in. You can then just copy, paste, and modify the field that the tool is translating.

If more than that, then the multi field formula tool is going to be the fastest approach.

Best, Esther

Meteoroid

I really thought you all solved my issue with this workflow... but it still doesn't work with my input data apparently...  some errors below

 

 Multi-Field Formula (1) DATETIMEPARSE: Cannot convert "24-Jan-19" to a date/time with format "%m/%d/%Y":  Month number is out of range 1..12: '24-Jan-19'

 Multi-Field Formula (1) DATETIMEPARSE: Cannot convert "10-Aug-18" to a date/time with format "%m/%d/%Y":  Expected a number for Day: 'Aug-18'

 Multi-Field Formula (1) DATETIMEPARSE: Conversion Error Limit Reached

 

 

Meteoroid

LOL  stripping down the workflow to post this led me to my own answer.  I was trying to use the multi-tool to get to my desired date format.  I have to use the "current" format for the string ('%d-%b-%y' in this case) in the tool so it recognizes the incoming data first, then it converts it to a date.

Atom

Using Select tool does not work as desired, instead, need to use Multi-Field Formula to get the desired datatype conversion. See attached Workflow settings.Capture1.PNG

 

Labels