Mark Johnson Mark Johnson - 5 months ago 35
SQL Question

oracle sql date format -1 month

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.

thanks all.


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')