Haha... got the solution myself
--iif the month is below then then the format will be with zero example. June =06
Or simply, TO_CHAR(SYSDATE,'MM') would give you '06'
Just my 2 cents.
I dare to disagree; as of my knowledge TO_CHAR() always returns the shortest valid string representation of the given number.
So I suggest you use LPAD() to achieve the desired result:
LPad( To_Char( SysDate, 'MM'), 2, '0')
Maybe this works with PowerCenter 8.6.x; with PowerCenter 8.1.1 SP5 you definitely have to use LPad(), I've tried it the other minute.
Thanks for the hint; as soon as I have the chance to work with 8.6.x, I'll have to try this. This is really interesting.
Thanks and regards,
I am using the following expression to find out if current month is January. Any idea why?
but when I try to save the expression it throws me the following error
[IFF((TO_CHAR(SYSDATE,'MM')='01'),'2020','2021')]: : invalid function reference... >>>>IFF((TO_CHAR(SYSDATE,'MM')='01'),'2020','2021')<<<<
I meant any idea why the above error would occur.
Yes: you mis-spelled IIF, you keyed in IFF (with a double F) instead of IIF with a double I,
Thanks. Yes I figured out that was the issue. Once I spelled it correctly, it worked.