How can I use add_months with a date field to retrieve past data?

so, your invoice_date isn't actually a date?  It's a string?
you should get that fixed right away if possible.
first though, confirm it really is a string,  it's also quite likely it is a date and you're simply viewing it in that format.

However, to address your immediate concern,  I'm not sure how you can retrieve any data.
If you have a customer that hasn't been invoiced in 6 months, then how are they going to have a record in the invoice table for the previous month.  

Maybe I'm not understanding your question.

I guess it must be something like the following:

select * from invoices
where to_date(invoice_date,'mm/dd/yyyy') > add_months(sysdate,-6)
and received is null

where "received"  field is one, which confirms receiving of invoice by a customer

It is actually a date field and has been around for many years. it must be just the way it is viewed.

Rhonda,
    To use add_month to go back you use negative numbers:

select * from invoices
where custid in
(select custid from invoices
MINUS
select custid from invoices where to_date(invoice_date,'MM/DD/YYYY') >= add_months(sysdate,-6))
/

You want to eliminate the customers who've had an invoice in the last six months.  That's done by using a minus operation here.

Good luck!

Rhonda,
   Sorry, I just saw the clarification on the date field.  Just ignore the to_date.  The rest should apply.

Good luck!

sounds like you need more than one column

how do you find an invoice row that is for the previous month?
If that's invoice_date, then what is the difference between a row from last month and
all other rows in the last 6 months?  

Rhonda,
    Do you mean you want to get a list of the customers who have an invoice in the last month, but it's the first one they've gotten in six months?  That would be just a minor change to the query I gave you:

select custid, min(invoice_date) earliest_invoice_this_month,count(*) numberInvoices
from invoices
where invoice_date > add_months(sysdate,-1) and
custid in
(select custid from invoices where invoice_date > add_months(sysdate,-1)
MINUS
select custid from invoices where to_date(invoice_date,'MM/DD/YYYY') >= add_months(sysdate,-6))
group by custid
/

Good luck!

ah, now that I see the answer, I understand the question.   :)

Thanks for clarifying awking00, good work!