Convert String to Date format "01-May-17"...

[florayaoyao]

Hi Alteryx gurus, 

I am trying to convert a string field to date type.  The data value is in format like  05-May-17 as a string. I tried the formula below, but the results shows as "2017-05-1"  that missing partial value.. 

DateTimeParse([GL_DATE],"%d-%b-%y") 

In the formula, I could not change the size field 9 to 10... 

Someone please help. Thank you!! 

Flora 

[SeanAdams · Accepted solution]

Hey Flora,

There's a couple of ways to do this, but the most simple is to use the data convert tool - screenshot and flow below.   Second option to follow.

[SeanAdams]

Other option, if you wanted to do this the slightly longer way is:

- use text-to-columns to split the year, month, day into their own columns (string type)

- where the year is shorter than 4 characters, add 20 or 19 to it using a formula

- bring these back into a single string of format 2017-05-10 using a formula tool.   

- you can then just use a select tool to convert this to a date since this is Alteryx's internal representation of a date (yyyy-mm-dd)

Hopefully this gets you to a solution :-)

Cheers

Sean

[Joe_Mako · Accepted solution]

The Multi-Field Formula tool allows you to change the data type of a field:

[NicoleJohnson]

It sounds like a field format issue rather than a date formula issue - can you insert a Select tool before your formula tool and increase the [GL_DATE] field size to 10 ahead of time? Alternatively, if you create a new field in the formula tool instead of reusing [GL_DATE], and then add a Select tool after to swap out the new field for the old one, you'll be able to specify your field type and length in the new formula.

Hope that helps! :)

NJ

[florayaoyao]

Thanks Sean! for sharing many options here.  The DateTime Parse tool works! 

[florayaoyao]

Thank you Joe.  This is what exactly I am looking for since I have more than 1 data type fields with this issue. 

Thank you! 

Flora 

[Lennylin]

It help me, thanks.