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

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

Date Time

Accepted answers

SeanAdams

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.

DateConvert.yxmd

Joe_Mako

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

change data type.png

change data type.yxmd

All comments

SeanAdams

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.

DateConvert.yxmd

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

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

change data type.png

change data type.yxmd

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!

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703