CODEWITHSUNDEEP
sqloracle-databasedateformat
Mark Johnson
Mark Johnson

Reputation: 103

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.

Upvotes: 0

Views: 378

Answers (2)

Jim Macaulay
Jim Macaulay

Reputation: 5141


Hi,
You can use below logic,

(trunc(itny.DEP_DATEe,'mm'),-1)

mm -1 derives you previous month. Apply this syntax to your requirement. Need to convert your format to date, just take the day values as some default

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269953

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

Upvotes: 2

Related Questions