Parsing ISO8601 Formatted Dates?

I'm having a heck of a time parsing some really standard date formats. I've got timestamps coming out of an API in full ISO8601 format:

2017-03-30T09:57:45:963-0700
2017-04-13T15:48:46:034-0700
2017-01-16T14:42:05:811-0800
2017-01-16T14:41:04:640-0800
2017-01-16T14:41:04:640-0800

From what I've found, Alteryx doesn't support milliseconds or timezones, so I've had to throw down a Regex parser to tokenize the dates into their elements:

(\d{4})-(\d{2})-(\d{2})T(\d{2})\:(\d{2})\:(\d{2}):(\d{3})([\-\+]\d{4})

and then use a formula to pull in the values Alteryx can understand and format it:

DateTimeParse([EntryDate_Year] + "-" +
	[EntryDate_Month] + "-" +
	[EntryDate_Day] + "T" +
	[EntryDate_Hour] + ":" +
	[EntryDate_Minute] +":" +
	[EntryDate_Second] + 
	[EntryDate_Timezone],"%Y-%m-%dT%H:%M:%S%z")

Is there a better way, or a feature request I should be voting up?

Regex

Parse

Date Time

Accepted answers

Philip

The attached is a macro we use to handle these cases. Insert and use like you use other tools. Let me know if you have any questions.

Date Time Offset to UTC.yxmc

All comments

Philip

The attached is a macro we use to handle these cases. Insert and use like you use other tools. Let me know if you have any questions.

Date Time Offset to UTC.yxmc

SeanAdams

Hey @braxtone - there are also a few ideas that you can contribute to too, several of them around decomposing or recomposing dates; and one about rethinking the way that dates are treated in Alteryx (since there are different date treatments for date parsing; date construction; date trimming; date adding; etc).

There's none that specifically addresses ISO date format, but I reckon you should add it.

Here's the full list of ideas in the IDEAS board related directly to date handlin. Have a look at these, and it would be good to get your thoughts and support on them:

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Refactor-Date-time-treatment/idi-p/46420

https://community.alteryx.com/t5/Alteryx-Product-Ideas/DateTime-Tool-Enhancements-Convert-Multiple-FIelds-amp-Overwrite/idi-p/23965

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Time-Based-Function/idi-p/4842

https://community.alteryx.com/t5/Alteryx-Product-Ideas/More-forgiving-evaluations-of-Date-and-DateTime-data-types/idi-p/51478

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Have-a-way-to-easily-get-date-parts-out-of-a-date-field/idi-p/10073

https://community.alteryx.com/t5/Alteryx-Product-Ideas/DateTimeTrim-Function-Add-Week-Trimming/idi-p/21850

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Add-DateTime-type-with-milliseconds/idi-p/20005

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Alteryx-making-dating-easier/idi-p/20625

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Make-d-and-m-in-DateTimeFormula-work-if-no-leading-0-in-all/idi-p/18619

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Null-Date-Check/idi-p/13116

https://community.alteryx.com/t5/Alteryx-Product-Ideas/CreateDate-Function/idi-p/55171

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Update-existing-field-in-DateTime-tool/idi-p/48599

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Allow-Date-DateTime-Time-output-from-Regex-Parse/idi-p/15738

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Update-existing-field-in-DateTime-tool/idi-p/48599

https://community.alteryx.com/t5/Alteryx-Product-Ideas/DateTimeFirstOfWeek-Formula-Function/idi-p/1487

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Date-Time-Formatting-Beginning-with-2-Digit-Year/idi-p/1441

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Have-a-Time-Question-in-Analytic-Apps/idi-p/1437

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Impute-Dates/idi-p/1509

https://community.alteryx.com/t5/Alteryx-Product-Ideas/DateTimeTrim-for-Year/idi-p/35805

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Next-potential-improvement-to-formula-tools/idi-p/51766

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Improvements-to-the-Select-Tool/idi-p/33834

https://community.alteryx.com/t5/Alteryx-Product-Ideas/random-inputs/idi-p/54222

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Field-Sumary-tool-should-report-all-data-types/idi-p/14280

RyanTM

Thank, this is super helpful. Note that the original poster's data (and mine) does not have a : between the hh and mm in the timezone offset. I've attached an updated version of the macro which should handle both.

Date Time Offset to UTC.yxmc

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

AdamR 335