Filter a string column as if it was a date / adjust to read as date so I can filter?

[Lili7891]

I've tried searching the forums here but the questions seem to be more about converting existing date formats to Month/Year. 

My issue is - I have a string column which has a period in the format of Month/Year (e.g. JAN 2021) - If I convert to date using the Select tool - the field goes blank, and if I use the DateTime tool - it doesn't have an option to recognise as Month / Year  and I do not want to add a random date at the start if I can help it.

The reason I am trying to convert is because I want to filter whatever data gets fed in to only show if the Month and Year is after a certain point e.g. after July 2020. Is there a way i can do this?

[Emil_Kos · Accepted solution]

Hi @Lili7891,

You should use this formula:

DateTimeParse([Date],'%b %Y')

Please see the workflow attached.

[echuong1]

A properly formatted date in Alteryx will be YYYY-MM-DD. That being said, you do need to include all three pieces in some manner.

You cannot simply change a datatype to a date in a Select, because of the variety of date formats available. For example, if I have 05/06/2020, it could represent either May 6 or June 5, depending on if the month is first or second. That is why you have to specify an incoming format in the DateTime tool.

That being said, you can specify any incoming format in the DateTime tool. It doesn't have to match one of the predefined formats. There is a custom option at the bottom. You can specify the format, and if it is missing one of the necessary pieces, it will fill it in. For example, I used your example of an abbreviated month followed by the year. It automatically filled in the day to be 01. 

Given that you're trying to filter after a certain date, using the first of the month is appropriate. So if I chose July 2020, it would filter for any record after July 1, 2020.

Date functions can be found here:

https://help.alteryx.com/current/designer/datetime-functions