I thought of sharing a learning from a mistake I recently made when using CalcMgrDateToString function and hope you do not make the same. The CalcMgrDateToString accepts the date format which we like to return, say yyyy for years and MMM in Jan, Feb etc.
The options for the date format can be picked up from Java’s SimpleDateFormat class and this is where things get ignored sometimes. The important thing to note here is the usage of yyyy vs YYYY as the parameter to the function. The function accepts both YYYY (all uppercase) as well yyyy (all lowercase) and both of these parameters return a different value. We might not see any difference in the returned value unless we use it with Dec 31st of any year.
Let’s give it a try and see what happens. In the fist snapshot I am using simple code to return the year date part for 31st Dec 2019. Since Essbase stores the date values as YYYYMMDD, i have directly used the value 20191231 as first parameter and passed the second parameter as yyyy (all lowercase). Returned value is 2019.
In the second snapshot, I replace the second parameter with YYYY (all uppercase). Returned value is 2020.
Parameter YYYY takes the week year and 31st Dec 2019 falls on a Tuesday making it a day of the week in 2020. A small difference between lowercase and uppercase could essentially cause execution of calculation logic for an additional year. If we try the same thing with 31st Dec 2022, we would see both parameters return the same value as this date falls in the same week year.
Cheers !!!
