DateTime Conversion failing

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! 😊

Date Time

Accepted answers

atcodedog05

Hi @lfaust

I removed space and comma it started working. There might have some special characters.

Workflow:

Hope this helps : )

Date Prase.yxmd

All comments

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 : )

airdate.yxmd

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.

20210816-AirDate.yxmd

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?

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.

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')

atcodedog05

Hi @lfaust

I removed space and comma it started working. There might have some special characters.

Workflow:

Hope this helps : )

Date Prase.yxmd

binay2448

Find attached solution

AirDate _Binay2448.yxzp

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335