DateTimeParse Issue - year 1800s not parsing
- 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 currently using the following formula to parse dates all into yyyy-mm-dd format so I can apply the relevant filter to see the data I need. However, when testing, I am finding that it is parsing 1900 and 2000 dates fine, but the 1800 dates have an error. On the alteryx functions the earliest date should go up to 1400, so not sure why I get this error.
Error Message Example:
ConvError: Formula (59): DATETIMEPARSE: Cannot convert "06/06/1800" to a date/time with format "%Y/%d-%m": Month number is out of range 1..12: '1800'
Formula being used:
DateTimeParse([Date of Birth],"%Y/%d-%m")
Appreciate if someone could assist!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @HarryRoles,
Please try this formula:
DateTimeParse([Date of Birth],"%Y/%d-%M")
I have changed m with M
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi Emil,
I changed to M but its now changing the 06/06/1800 value to "2006-01-06 00:18:00"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @HarryRoles,
You are right:
DateTimeParse([Date of Birth],"%d/%m/%y")
I am still learning date parsing.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi Emil,
Although this seems to have fixed the parsing for 1800 year values, it now seems to make the 1900 & 2000 values null / blank in the browse once run.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @HarryRoles,
Can you share the data sample with me.
For me it looks like it works perfectly.
Please check my workflow.
Maybe you need to switch Month with date in the formula?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi,
Thanks for the help. The tool was useful, however I'm now seeing something really weird.
When I input my file, all of the dates are in 01/01/1900 format, and obviously my goal is to change them to 1900-01-01. For some reason when the file is loaded, a handful of records get auto formatted in Alteryx to the dash format, which is making them not parse into a new column as I want.
Any suggestions?
Have attached examples of it changing and the warning message i get.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey,
Okay, that makes sense. My end goal here is to be able to filter to see dates greater than 100 years, and dates within the last 5 years. How would I do this if there are two fields with DOBs in (the original one where some auto format, and the second one being created as part of the parsing?
Thank you very much for the help!
