Re: Last Week

From: Martin Burbridge <pobox002_at_bebub.com>
Date: 28 Jan 2003 06:12:10 -0800
Message-ID: <45a06b65.0301280612.59e17799@posting.google.com>

va3wmh_at_rac.ca (Bill) wrote in message news:<68a1410e.0301271506.8e4dfbd_at_posting.google.com>...
> "Jim Kennedy" wrote
>
> > select ... from ... where
> > the_date_field >= trunc((sysdate-7)) and
> > the_date_field<=trunc(sysdate)
> >
> > That gets you what you want for the last 7 days. sysdate is now. doing a
> > trunc on it gets you today at midnight, subtracting whole numbers subtracts
> > days. Not sure what your definition of last week is, but that would help or
> >
> > select ... from ... where
> > to_char(the_date_field ,'IW')>=(to_char(sysdate,'IW')-1) and
> > to_char(the_date_field ,'IW')<=(to_char(sysdate,'IW')-1)
> >
> > to_char( date, date_format) IW is the week number. The above won't work
> > when you go over a year, but this should give you a start.
> > Jim
>
> Thanks Jim;
>
> I didn't really intend to start a conflict between MS Access & Oracle
> fans. I've used Oracle years ago but haven't recently played with it.
> I don't wish to embarass any one at the office but if the user isn't
> happy with the last seven days, which is what I got instead of last
> week. I'll need your help again.
>
> I'm sorry I didn't specify more exactly what I meant by last week.
> What I'm looking for is to select the records between the previous
> Sunday and the previous Saturday. For example, last week's records
> when selected to day would include all records with a date between 19
> Jan and 25 Jan inclusive.

Ah, all it needed was a clear definition of what last week actually refers to. In this case you can use trunc with the 'd' format model to truncate the date to the first day of the week, then deduct 7 days to get the first day of the previous week.

SQL> alter session set nls_date_format='DD-MON-YY HH24:MI:SS';

Session altered.

SQL> select trunc(sysdate,'d')-7 "Week Start",   2 trunc(sysdate,'d') "Week End" from dual;

Week Start Week End

------------------ ------------------

19-JAN-03 00:00:00 26-JAN-03 00:00:00 SQL> Note the time portions of the date are midnight. Your query would then use the range >= Week Start and < Week End.

The trunc function is very useful with dates and much underused, it can give you along with start of week, start of month, quarter or year.

And its helpfully explained in Oracle's documentation.

Which as well as being free, though requiring registration, has recently been discovered on this ng to be both celeb studded and rocking.

trunc (URL may wrap)

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions176a.htm#1128639

your gateway to the stars

http://tahiti.oracle.com

Martin Received on Tue Jan 28 2003 - 08:12:10 CST