Converting a date format

[MJ54]

I am bringing in values with the abbreviations at the end of each day (example August 2nd 2021 or August 25th 2021)

How do I build in a function to remove the “th”, “st”, etc at the end of the day number and replace it with a comma?

[atcodedog05]

Hi @MJ54 

You can use replace function like this. 

REGEX_Replace([Date], "(.+\d)(:?th|st|nd)\s(.+)", "$1 $3")  the way i have added nd similar add new by adding it as |ths

Workflow:

Hope this helps : )

[Luke_C]

Hi @MJ54 

Here's another option - I used regex to parse the data we want and then concatenated in a formula tool. 

@atcodedog05  - it looks like your solution is cutting of the 'st' in August. I wonder if there's a way to tweak it since doing a direct replace with a comma is cleaner.

[atcodedog05]

Hi @Luke_C 

Thank you for notifying me that, I guess i miss that out 😅 I have updated with a working solution 🙂 Thank you again.

[Maskell_Rascal]

Hey @MJ54

Both the solutions provided will work, but this one does the same thing in one formula without needing RegEx. 

DateTimeFormat(DateTimeParse([Field1],'%B %d** %Y'),'%B %d, %Y')

Attached is a copy for you to try. 

Cheers!

Phil

[Luke_C]

@Maskell_Rascal Awesome! I didn't know there was this wildcard functionality in the datetime functions. 

[atcodedog05]

+1 with @Luke_C 

@Maskell_Rascal even I was unsure that datetimeparse() supports wildcard functionality 🙂

Thanks for sharing 😀

[Qiu]

@atcodedog05 @Maskell_Rascal 
It is new for me also. Thanks for the wild card information.

[Maskell_Rascal]

@Qiu, @atcodedog05, @Luke_C 

Glad I could share with you guys!

This is one of those little known features within Alteryx for date conversions. While it doesn't specifically say in the help documentation for date functions that an asterisk can be used as a wildcard within the formula tool, it does state you can use it in the DateTime tool when using the custom format option.