On Thu, 4 Feb 1999, Mark VanHorn wrote:
>      I have a question for you.  Suppose I want to run a query at any given
>      time during the current month which only generates activity for any
>      rows that were added (mytable.add_dte)in the prior month.  How would
>      the code look for this?  I can start:
>
>      SELECT *
>      FROM mytable m
>      WHERE m.add_dte ????????prior month?????????;
>
>      I always enjoy looking around your site.
>
>      Thanks,
>      Mark Van Horn
Mark,
       
To find the month value of the field, issue:
SELECT to_char(ADD_DTE,'MM') FROM mytable;

I am sure that there are more compact ways to do this, but to find the
value of the prior month, issue:
SELECT
decode(to_char(sysdate,'MM'),'01','12','02','01','03','02','04','03','05',
       '04','06','05','07','06','08','07','09','08',
       '10','09','11','10','12','11')
FROM mytable;

Now, to do your query (find all records from the previous month), issue:
SELECT * FROM mytable m
WHERE to_char(m.add_dte,'MM') =
decode(to_char(sysdate,'MM'),'01','12','02','01','03','02','04','03','05',
       '04','06','05','07','06','08','07','09','08',
       '10','09','11','10','12','11')

Note that this results in values from the prior month (ie ALL Januarys for
all years). You will have to modify this code for working with the
previous month of the current year (unless it is January, then it is the
December of the previous year). I did not know if that is what you meant.

Best regards,
       
-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->   
<-> For 250+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             www.arikaplan.com                                 <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

Back to Ari Kaplan's Home Page dex.htm"> Back to Ari Kaplan's Home Page