Ternary operation to handle diff date formats

[UrsVinny]

The source file contains Reporting Period value in diff date format.
Aug-2022
Aug-22
AUG 2022
August-2022
I am using the below ternary expression to handle the above mentioned date pattern.
isNaN(Date.parse($RPT_MONTH,‘MMM-yyyy’)) || isNaN(Date.parse($RPT_MONTH,‘MMM-yy’)) ? $RPT_MONTH : Date.parse($RPT_MONTH,‘MMM-yy’).toLocaleDateString({“format”:“MMM yyyy”})

But when the value is in August-2022 the above code is not converting it into AUG 2022

Any kind of help will be highly appreciated.

Cheers
Vinny

[bojanvelevski]

Hi @UrsVinny ,

You can always extend the ternary operator with another nested ternary, but that could very easily become a complex and unsustainable expression.

I would recommend an expression library with a function that will check the date field for all possible formats.

That way you can easily add a new format in the future if needed.

Regards

[koryknick]

@UrsVinny - Sorry I originally posted a little too quickly and didn’t validate my reply.

Here is a small sample pipeline using the Conditional snap to test if it’s a 2-or-4 digit year before doing the Date.parse()
Community 13660 - Date parsing_2022_10_19.slp (5.4 KB)

It’s pretty easy to add expressions in the Conditional. As a general rule, I typically revert to the Conditional snap when the ternary logic requires more than one test since ternary gets hard to decipher after just a couple levels.

Hope this helps!

[del]

I’m just playing with regex ideas for this (because who doesn’t like to - regex is like play doh! 😄), I think this will work for a mapper if you know it is a single delimiter being a space or a dash:

Date.parse($RPT_MONTH.replace(/^([^ -]+).*(\d{2})(\d{2})$/,"$1-$2$3"),'MMM-yy').toLocaleDateString({"format":"MMM yyyy"})

But you could cover your bases in case the delimiter is not a space or dash or no delimiter at all (ex. Aug.2022 or Aug22) with either one of these two:

Date.parse($RPT_MONTH.replace(/^([^\d]{3}).*(\d{2})(\d{2})$/,"$1-$2$3"),'MMM-yy').toLocaleDateString({"format":"MMM yyyy"})
Date.parse($RPT_MONTH.replace(/^([a-zA-Z]+).*(\d{2})(\d{2})$/,"$1-$2$3"),'MMM-yy').toLocaleDateString({"format":"MMM yyyy"})

Disclaimer: testing works for me with the values of the original post and a few I threw in of my own. All potential cases are not fully tested.

[del]

Follow-up to my post: In my testing, I found that Date.parse() format of “MMM-yy” works correctly with both 2-digit and 4-digit years, so it may not be necessary to differentiate in your logic.