When preparing a data set for analysis one of the common issues is the date and time stamp used. Being able to prepare the data for use in analysis later can be confusing at time with all the different ways the data can be presented. This process is simplified greatly using the Date Parse tool in Alteryx but the particular data set you have can often format the date in a different way and then using the ‘DateTimeParse’ formula will be needed.

The DateTime Tool

The Date time tool is extremely useful for converting standard date strings into a date field. With the tool you simply define the conversion you want (string to Date/Time or Date/Time to string), select the field with your date, choose what the field looks like in the string e.g. dd-MM-yyyy (19-10-2014) and Define the output field.If your strings match this format the tool works really well. It is quick and simplifies the process nicely. The problem is the list of fields doesn’t always match your date field (there is no yyyyMMdd option there at all) so you won't always get the exact match you need.[caption id='attachment_4186' align='alignnone' width='300'] Configuration Menu of the DateTime Tool[/caption] 

The Formula Tool

The other, more flexible way of parsing the date is to use the ‘DateTimeParse(dt,f)’ formula. The syntax for this formula is relatively simple. The dt portion of the syntax is the string field where the date is stored, the f portion is the format you want the date after it has been parsed.Now, while that sounds simple there are a couple of catches. The format you want output must map onto the string perfectly. For example, if you have a string in the ISO date format: ‘2015-07-16T14:50:15-07:00’ your output field must match.Lets look at the each part. ‘2015-07-15’ is a normal date following the year-month-day pattern. So to parse that the code would be: %Y-%m-%d note the hyphens in the output match the hyphens in the string (for a list of the syntax codes have a read through the Date/Time Operations help file there is a table near the end).Next is a common catch (at least for me) the character ‘T’ needs to be represented in the output as well.So far we have ‘%Y-%m-%dT’. Then next step is the time portion. Again, we use the help with instructions for the syntax required, for our example ‘14:50:15’ would become ‘%H:%M:%S’. It is important to remember these specifiers are case sensitive. As I was writing this post I mistyped %H as %h breaking the formula. It is an error that is very easy to make and it took a second set of eyes to see my mistake.The final part of the field is the ‘-07:00’  this portion matches up to the time zone and has a specifier of %Z or %z (the one case that is not case sensitive). The Timezone of -07:00 matches to American Pacific Daylight Time, while a Timezone of ‘Z’ represents Zulu or local time.When all the portions are combined the formula results in ‘%Y-%m-%dT%H:%M:%S%z’ to parse ‘2015-07-16T14:50:15-07:00’. This example covers most of the different components of parsing date/time strings.Have a look at the packaged module here for more examples.In a future post I will have a look at some other examples such as Unix time, and dates missing leading Zeros.Until then.