Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

florayaoyao
8 - Asteroid

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 

 

3.JPG1.JPG

 

7 REPLIES 7
SeanAdams
17 - Castor
17 - Castor

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.

 

2017-05-10_23-07-55.png

SeanAdams
17 - Castor
17 - Castor

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
12 - Quasar

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

 

change data type.png

NicoleJohnson
ACE Emeritus
ACE Emeritus
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
8 - Asteroid

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

florayaoyao
8 - Asteroid

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
5 - Atom

It help me, thanks.

Labels