Re: Question - Passing a date

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 -- Received on Fri Sep 01 2006 - 08:22:22 CDT