Hi,

I have been asked to author a report for Finance (having not done this
before) and I want to pass set up a view which will take the assembled
data from 01/09/05 until the End of the month previous ie 31/08/06 but
up until Midnight. THen next month for September.

Is there a way to get the previous month's date by using TO_DATE
Sysdate and minusing the month by 1 (The report will always be run on
the 1st of the next month).

Any and all help would be appreciated.

VB wrote:
> Hi,
>
> I have been asked to author a report for Finance (having not done this
> before) and I want to pass set up a view which will take the assembled
> data from 01/09/05 until the End of the month previous ie 31/08/06 but
> up until Midnight. THen next month for September.
>
> Is there a way to get the previous month's date by using TO_DATE
> Sysdate and minusing the month by 1 (The report will always be run on
> the 1st of the next month).
>
> Any and all help would be appreciated.

Is this helpful? ...

select sysdate now, add_months(sysdate, -1) one_month_before from dual

best,
Martin

Martin T. wrote:
> VB wrote:
> > Hi,
> >
> > I have been asked to author a report for Finance (having not done this
> > before) and I want to pass set up a view which will take the assembled
> > data from 01/09/05 until the End of the month previous ie 31/08/06 but
> > up until Midnight. THen next month for September.
> >
> > Is there a way to get the previous month's date by using TO_DATE
> > Sysdate and minusing the month by 1 (The report will always be run on
> > the 1st of the next month).
> >
> > Any and all help would be appreciated.
>
> Is this helpful? ...
>
> select sysdate now, add_months(sysdate, -1) one_month_before from dual
>
> best,
> Martin

Besides the add_months function Martin pointed out I would like to
point out that the job might not always be submitted on the first due
to holidays, weekend schedules etc... so you might want to use
trunc(sysdate,'MM') which will set the result to midnight on the first
of the current month. Then the exact date the job is submitted will
not matter and you can code < trunc(sysdata.'MM') to always stop
processing with 23:59:59 of the prior month.

The last_day function may also prove useful to you at some time in the
future. You can find the DATE manipulation functions in the SQL
manual.

HTH -- Mark D Powell --