Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
MarqueeCrew
20 - Arcturus
20 - Arcturus

I have a dataset that has a bunch of date fields 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,

@fitzviz

For those who need to see the answer to the question first, you can use a formula like this or change [Field1] to [_CurrentField_] and use a Multi-Field Formula tool:

 

DateTimeParse([Field1],"%d-%b-%y")

How did I know the answer to this question?  Was it because I am an ACE?  Do I have a photographic memory?  None of these are right.  I knew the answer because I knew where to lookup the date formats (DateTime Format Help).  Based upon this question and the presence of frustration, I have opted to write this blog and (as a present for those who read it) I have included an incubator macro (something that I'm playing with) that isn't generally available.  My solution post (as of now) hasn't been starred or accepted as a solution, so I don't know if it helped Mike, but let's assume that it has.  The community has literally 1,000 "Date" posts in the last year.  While I could make a bundle of SOLVES if this continues, it does seem like there are an awful lot of questions regarding dates (note: not all are transformation questions).

 

Alternative approaches to working with dates include the BB Date tool and the DateTime tool.  I've even created an idea (Alteryx making dating easier) that you can STAR to change the way that the product handles dates.  Until dating becomes easier, here is a new approach to handling dates.

 

Read the manual: Format strings

Date/Time format strings are used by the DateTimeParse and DateTimeFormat functions to tell Alteryx how to parse (read) or format (write) date/time values. Format strings are comprised of specifiers and separators.

 

Please note that I didn't include the full help article here.  I was even going to show fewer rows (e.g. remove time rows) but wanted everyone to get a good taste for the possibilities.  The help article shows both how to convert strings to dates with DateTimeParse() as well as how to format dates back into strings.  For simplicity, I am looking to help with the conversion to date fields here.  So let's focus on the parse function.

 

The syntax is DateTimeParse([Fieldname],<Format>).  This means that the format can either be a variable in the incoming data or it can be a string.  If it is a string, then the formatted function would look like this for a sample start date field with a value of 23-Jul-1990:

 

DateTimeParse([Start Date], "%d-%b-%Y")

%d was chosen as the day of the month then '-' was the delimiter used followed by %b as the abbreviated month name followed by '-' followed by %Y as the 4-digit year.  As long as all three components of the date are present in the data, you should be able to construct a formula that meets your specific needs.

 

capture.png

 

For fun I created a macro that would sample the first 100,000 records from an input file and would find the most prevalent description of the incoming date formats (for non-null values).  It can discern between 04/01/2018 as April 1st and January 4th (localization) if dates exist that make this clear (e.g. 04/13/2018) in that date field.  If multiple formats exist for incoming dates, the Pct (percentage) column falls below 100%.  Instead of showing each format, my thought was that you'll need to do some data investigation to better understand the source of the data.  This "tool" macro can help you to understand the Parse function as a beginner and prepare you to better help others in the future.

 

What formats does it help with?  Many formats are supported (the delimiter is any non-word character):

  • 10-23-2018
  • 10-23-18
  • 23-10-2018
  • 23-10-18
  • Oct-23-2018
  • Oct-23-18
  • 23-Oct-2018
  • 23-Oct-18
  • 2018-23-10
  • 18-23-10
  • 2018-Oct-23
  • 18-Oct-23

I didn't try to make all formats and I honestly didn't test all of these formats either.  What I did do was create a "roughly right" macro and would ask you to test it out for me.  See if it helps you and suggest ways to improve it.  Take a look at the macro (open it) and see how I got the results.  Here is a little logic for you:

 

REGEX_Match([Field1],"\l{3}\W+([1-9]|0[1-9]|1\d|2\d|3[01])\W+(19\d{2}|2\d{3})")

If this statement is true, then the format is:  "%b-%d-%Y" where the - is whichever delimiter is really in your data.

 

capture.png

 

I calculate a series of formats (e.g. b-d-YY) as boolean variables (True or False) and then convert the True values to expressions.  I do a little magic and then pop out my guess for the format.  Granted, I could actually transform the data and could allow you to do this for more than one field at a time.  But alas, I'm not so inclined.  I do want to help the Alteryx community.  I welcome your feedback here and hope that if you are reading this post that it not only helps you to solve your current challenge but also gives you a forum to voice your opinions.  Please feel free to download and to try the tool out.  If there are problems here or if you think of ways to improve this tool, then I might publish a version as part of the CReW macros.

 

Cheers,

Mark

Comments
MCDR929
8 - Asteroid
Hello - thank you for all the useful information here, but I'm still struggling with datetimeadd after a datetime parse. I'm working with a time only. It was originally in text form, so 8:00. I know this is actually 8:00 PM (all my times are PM). I parse this to HH:mm:ss, which gives me the time 08:00. What I want to do is simply add 12 hours to this time (giving me the 24-hour time). But whenever I try datetimeadd , the results are null. I've even tried taking the parsed time and using a datetimeformat first, but that only creates null results as well. Suggestions? This should be very straightforward, so I'm not sure why I can't just add 12 hours to a time that came from the datetime parse tool.
RodLight
8 - Asteroid

I believe DateTimeAdd actually requires a DateTime value...so having just 08:00 won't work. What I have done in the past is create an DateTime using an arbitrary date, then use DateTimeAdd, and have the resulting Data type be "Time" (which gets rid of the date.

So in a formula tool, create DateTime as...

"2018-01-01 " + [Time]

Then create your PM Time value with...

DateTimeAdd([DateTime],12,"hour")

papalow
8 - Asteroid

Yes, at times I am annoyed with how Alteryx handles dates.  I thought it was just my Alteryx beginner status and that over time it would pass.  Reading the manual helps but the learning curve is steeper than I would like.  Thanks for your post.

Elias
6 - Meteoroid

Thank you! Bookmarked!

a420117
5 - Atom

Hi All

Newbie here....just building my 1st workflows. I've seen some successes but am stuck at what seems like it should be a simple task.  Using the Input data tool and connecting to an Oracle table....rather large volume of records. I want to filter by date range on the input to cut down on processing time.  This is how I've configured it. Returns no rows. what am I doing wrong?

 

query builder input.jpg

 

ddiesel
13 - Pulsar
13 - Pulsar

Thanks @MarqueeCrew for providing this invaluable guide to dates! I have this page bookmarked and refer to it frequently.

tessaenns
9 - Comet

Great article!

 

Thank you for identifying the Alteryx Documents as a pain point. It does a good job of translating the code into layman's terms, however, it does not explain how to interact with the functions. So until one learns how to interact with the functions and how the functions interact with each other it is essentially useless and confusing to the majority of people stuck in this spot.

 

And the mass amount of comments, in which you alluded to and the comments on this article even, can attest to that.

 

As always, the community appreciates your support.

 

Cheers Mark!

-Tessa Enns

papalow
8 - Asteroid

@amitkellawan 

This is a great resource for wrangling dates