โ10-19-2022 02:31 AM
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
โ10-19-2022 02:59 PM
@del - did you check to ensure it was the right century? The parse works but I think the year is wrong.
โ10-19-2022 03:41 PM
The century is a good call-out and something to consider for the case. I did consider the century and Iโm leaving the century of the two-digit year to be determined by the Date.parse() interpreter as I donโt know the actual use case, the source data, or full requirements of whether it is intended to be historical or future/predictive reporting.
In any case, the โ$2โ replace output (of the above) could be replaced by a hard-coded โ20โ value or other round-of-century logic calculation if the date is always future dated and Date.parse() picks incorrectly for certain scenarios. My testing shows Date.parse() chooses the current century for around 19 years added (41 converts to 2041) and then backdates a century about 20 years added (42 converts to 1942).
Meanwhile, I shortened the regex further with current 2-digit logic - producing the same results as the others . (Like I said - regex is Play Doh)
Date.parse($RPT_MONTH.replace(/^(.{3})[^\d]*(\d{2,4})$/,"$1-$2"),'MMM-yy') .toLocaleDateString({"format":"MMM yyyy"})
(โ๏ธ 40-minute later edit to code snippet to remove inserted hard-coded value)
Hereโs some testing output:
โ10-19-2022 04:51 PM
Great solution. Love the regex usage. Thanks for the response to my query!
โ10-20-2022 07:06 AM
First thing I did this morning was reviewed my proposed solution. This solution is feasible for narrow windows of time, which would be the assumption required to use it. However, there is a caveat on the century concern, which Kory brought up first, which is that if weโre reading a large window of time (for instance, say weโre trending inflation since WWII {I know, an extreme case, but makes the point}), then looking at โAUG-42โ today using Date.parse() would result in โ1942โ, but if we process the exact same dataset this time next year, it will result as โ2042โ due to Date.parse() behavior around 19 vs. 20 years out. Very unreliable in that way.
So, use with caution.
Soapbox warningโฆ I canโt figure out how we rounded Y2K and still have datasets that have 2-digit year representations. In my opinion, this should be fixed at the data source, where it is broken, and not rely upon fuzzy logic to try and figure it out by best guess. While solutioning this, I would have so many questions and caveats with which to annoy the requesting stakeholders.
โ11-27-2022 05:59 AM
@koryknick
Sorry for responding so lateโฆI was able to fix the issue using ternary operator but nevertheless usage of regex is more kool than Ternary operator.
Thanks once again to each and every one.