DateTime Conversion failing

[lfaust]

Hi everyone,

I am newer to Alteryx and I am having some trouble getting the DateTime tool to convert my string in format Month dd, yyyy to mm/dd/yyyy. I did look on other posts for a solution and tried a few things but my column was still showing as null. In Excel, the date is not registered in date format. It is in general/text format and will not register as a date when I try to change it in that program.

Here is my workflow:

All of the other tools work fine and I am using them for different purposes than to change the date.

Here is the format of the dates I am trying to convert:

The type is V_WString.

TIA for the help! 😊

[atcodedog05]

Hi @lfaust 

Please change the column in datetime tool to Airdate.

Workflow:

I have attached a sample workflow which might help you.

Hope this helps : )

[CharlieS]

Hi @lfaust 

It looks like the target field of your date conversion is set to the "Episode Title" field of your data. Change this dropdown ("Select string field to convert") to the "Airdate" field you show in your other image. If that's not the case, they sharing a sample of data (or a screenshot of data) would help us understand what's going on. 

As far as other tools go, I prefer to do these types of conversions in the Formula tool. In the Formula tool, create a new field with a name like "Date" and set the field type to "Date" then try this expression:
DateTimeParse([Airdate],"%B %d,%Y")

EDIT: I just realized you're looking for mm/dd/yyyy output format. No problem, but the first thing to understand is that Alteryx uses the ISO8601 date format as the standard (yyyy-mm-dd) so we'll need to use that as an intermediary. 

1. Use the DateTimeParse( expression above to covert from "March 13, 2014" to the standard format of "2014-03-13"

2. Then use the DateTimeFormat( expression to covert from "2014-03-13" to "03/13/2014". You can do this in one expression like this, but note that this new field will have to be a string target. You can create a new field, or overwrite the existing [Airdate] string field. 

DateTimeFormat(DateTimeParse([Airdate],"%B %d,%Y"),"%m/%d/%Y")

Check out the attached workflow for an example of this. 

[lfaust]

Hi! Thank you for the quick reply. I originally did that but it must've changed when I was trying to configure a solution. I changed this field back to Airdate and it is still failing unfortunately. Do you know if there is anything else I could try?

[atcodedog05]

Hi @lfaust 

Can you try this configuration.

Hope this helps : )

[lfaust]

Hi, thank you for the quick reply. I downloaded your workflow and copied your formula and I am still only getting nulls unfortunately. Is there anything else I may need to change? I appreciate your help.

[atcodedog05]

Hi @lfaust 

Can you try this out.

Workflow:

Hope this helps : )

[lfaust]

Hi @atcodedog05

I tried this and I am still receiving a conversion error and the output column is still null.

Here is a screenshot, is there anything I still may be missing?

[lfaust]

Okay so I tried this one and again it's not working 😞

Thank you for all your help and attentiveness!

Here is a screenshot of what I'm seeing on my end:

I attached my input data as well if that helps.

[Luke_C]

Hi @lfaust 

This is an interesting one. The spaces in your date aren't really spaces, they are a 'no break space' based on putting it into https://www.babelstone.co.uk/Unicode/whatisit.html See below screenshot, I added a normal space at the end to demonstrate it's a different character. 

If you wrap airdate in a decomposeunicodeformatch() function, it will work. I.e:

datetimeformat(datetimeparse(DecomposeUnicodeForMatch([Airdate]),'%B %d, %Y'),'%m/%d/%Y')