DateTimeParse Issue - year 1800s not parsing

[HarryRoles]

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!

[Emil_Kos]

Hi @HarryRoles,

Please try this formula:

DateTimeParse([Date of Birth],"%Y/%d-%M")

I have changed m with M

[HarryRoles]

Hi Emil,

I changed to M but its now changing the 06/06/1800 value to "2006-01-06 00:18:00"

[Emil_Kos]

Hi @HarryRoles,

You are right:

DateTimeParse([Date of Birth],"%d/%m/%y")

I am still learning date parsing.

[HarryRoles]

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.

[Emil_Kos]

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? 

[echuong1]

Try using the datetime tool. It will allow you to do the conversions, and is a little more straightforward. All you have to do is select the format of the original date field.

In a formula, the following should work as well:

DateTimeParse([Field1],"%m/%d/%y")

[HarryRoles]

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.

[echuong1 · Accepted solution]

Hm, interesting!

A workaround for this is to use a conditional formula to pick up the correct value. Essentially, anything that is in the YYYY-MM-DD format will not be converted with the datetime() tool. 

[HarryRoles]

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!