Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL retrieving last two months
you might want to issue
recno,to_char(date,'MON') month,title
to just retrieve the month and then break on month.
-- Niall Litchfield Oracle DBA Audit Commission UK ***************************************** Please include version and platform and SQL where applicable It makes life easier and increases the likelihood of a good answer ****************************************** "David Lord" <davelord_at_hotmail.com> wrote in message news:afhnl2$mv$1_at_nntp-m01.news.aol.com...Received on Fri Jun 28 2002 - 08:25:33 CDT
> I don't think that will be precise enough.
>
> Something like this will be better
>
> SELECT recno, date, title
> FROM table
> WHERE date >= trunc(add_months (current_date, -1), 'month')
> /
>
> This way it doesn't matter where in the month you are or how many days in
> the month, you'll only get records in this month and the last, rather than
> records in the last 60 days which often will not be complete.
>
> As Daniel would say see http://tahiti.oracle.com for more information on
> date functions.
>
>
> "ImPrecise" <f_puhan_at_precise.com> wrote in message
> news:f_puhan-939910.08495128062002_at_vienna7.his.com...
> > In article <afhljn$cc$1_at_news.ost.eltele.no>,
> > "Per Magne" <perman_at_online.no> wrote:
> >
> > > I need to have an SQL selecting all records from the past two months
> (this
> > > month and last month).
> > >
> > > Anybody having a solution on this problem (especially December /
> January)?
> > >
> > >
> > >
> > >
> > >
> > > Ex. Table:
> > >
> > > RECNO int
> > >
> > > DATE datetime
> > >
> > > TITLE char(40)
> >
> > I don't currently have the time to hack out the code, but I suspect your
> > best way to do this is to use SYSDATE and SYSDATE - 60 in your WHERE
> > clause. Something like
> >
> > SELECT
> > RECNO, DATE, TITLE
> > FROM
> > table
> > WHERE
> > DATE between SYSDATE and (SYSDATE - 60)
> > /
> >
> > Hth,
> >
> > --
> > The underscore character does not belong in my address. You know the
> drill...
> > ***
> > Anyone sufficiently smart enough to configure and use USEnet for
research
> should
> > be smart enough to Read The Freakin' Documentation!
>
>