text to date conversion
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi all,
I am new to Alteryx, and looking for some help on the conversion of the following text strings to proper date format:
Input | Input format | expected output format |
210802 | yymmdd | 2021/08/02 |
2021214 | yyyyddd (last three digit in Julian format) | 2021/08/02 |
80321 | mmddyy | 2021/08/03 |
8022021 | mmddyyyy | 2021/08/02 |
21214 | yyddd (last three digit in Julian format) | 2021/08/02 |
Also how do I avoid this error "Cannot convert "0" to a date/time"? Should I replace 0 with null?
Thank you in advance for your help
Solved! Go to Solution.
- Labels:
- Date Time
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Are all these formats coming to you in one column from the same source, or is this just a grouping of examples formats you need help with?
Individually, these can be tackled with formulas or the date/time tool. If these are coming to you like this, where they are all intermixed, its going to make it a bit trickier to get these to all properly format.
If the later case is true, I would start by having a conversation with whatever monster decided to allow all those formats in a single column and see if they can clean it up before it gets to you lol.
Individually they can be parsed with these formulas.
Input | Input format | Alteryx formula |
210802 | yymmdd | DateTimeParse("20"+[Input],"%y%m%d") |
2021214 | yyyyddd (last three digit in Julian format) | DateTimeParse([Input],"%Y%j") |
80321 | mmddyy | DateTimeParse(PadLeft([Input],6,"0"),"%m%d%y") |
8022021 | mmddyyyy | DateTimeParse(PadLeft([Input],8,"0"),"%m%d%y") |
21214 | yyddd (last three digit in Julian format) | DateTimeParse("20"+[Input],"%y%j") |
The tricky part will be if these are all in one column. A formula to parse one of the date formats may work on the other but give you incorrect information as seen with the formula for the second Julian date.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you very much, SPetrie. They were just different formats I got from different fields. So your solution is what I am looking for.
By the way for "210802", what is the reason that this - DateTimeParse([Input],"%y%m%d") would not work?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
This is the explanation from Alteryx Documentation.
![](/skins/images/59DA3CA97FC6306BFE8B6DED203F3AC0/responsive_peak/images/icon_anonymous_message.png)