I want the DEP_DATE to match the PERIOD_TO for 1 month previou, using mm-yy match (the days are different)
Example DEP_DATE = JUL-16 to match with Period_to = JUN-16
in my Where clause...
and to_char(itny.DEP_DATE, 'mm-yy') = to_char(xch.PERIOD_TO,'mm-yy')-1
I can match exact month-year but it doesn't like the -1 at the end of period_to to get the month previous.
I've tried various to_date but no joy.
One method is to subtract the month before formatting as a string:
to_char(itny.DEP_DATE, 'mm-yy') = to_char(add_months(xch.PERIOD_TO, -1), 'mm-yy')
Another method is to just use date comparison:
itny.DEP_DATE >= add_months(trunc(xch.PERIOD_TO, 'MM'), -1) and itny.DEP_DATE < trunc(xch.PERIOD_TO, 'MM')