Modify a date to only Month

[rwicker]

How do you take a column of dates yyyymmdd and insert new column with just the Month. i.e. date 20180125 = Jan 2018 or 20180412 = Apr 2018). Once I have that format I will be using the transpose and cross tab tools to aggregate the months into columns so they are listed Jan 2018, Feb 2018, Mar 2018, etc.

Attached is an example of what the original document looks like and the other tab is how I need the outcome.

Any help is appreciated. Thanks!!

[Kenda · Accepted solution]

Hey @rwicker!

First, I would add a Select Tool to change your SCANDATE field to a string. 

Then, I would add a DateTime tool (in the parse category) and configure it like this:

Lastly, add a Formula Tool and create a new string field with the following expression:

DateTimeFormat([DateTime_Out],"%b %Y")

If you want your output in a different format, see this Alteryx help page: https://help.alteryx.com/current/index.htm#Reference/DateTimeFunctions.htm

Hope this helps!

[BenMoss · Accepted solution]

You should check out the datetimeformat() function which supports parsing out the different attributes that exist within a datetime field into one that suits you: https://help.alteryx.com/11.7/Reference/DateTimeFunctions.htm

However, first of all you will need to perform some conversion of your string date (yyyymmdd) to a true date (yyyy-mm-dd), this can be achieved using the datetime tool.

Once you have done this, say I have the date field 'date' and an example: 2018-04-27

I can use the datetimeformat() as mentioned above to parse this out.

So for example...

datetimeformat([Date],"%Y %m") would return me 2018 04.

If you check through the documentation noted above you will see that short months can be obtained using %b

Ben

[jrgo · Accepted solution]

@rwicker,

Take a look at the attached. First thing is that you need to parse the value into a DATE object. Afterwards you can use the DATETIMEFORMAT() function to convert it back out to a string in whatever format you'd like that Alteryx supports.

 

Hope this helps!

Jimmy