Re: Last Week

From: Frank <fvanbortel_at_netscape.net>
Date: Wed, 29 Jan 2003 20:27:35 +0100
Message-ID: <3E382B27.4040007@netscape.net>

Martin Burbridge wrote:
> 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

Be advised, weekends vary with NLS settings... In some countries weeks start on Monday (biblical, probably: 'and He rested on the 7th day' - that would be the last day, then).

Anyway. NLS settings matter (at least they did. Didn't check on your version - what version?)

Frank Received on Wed Jan 29 2003 - 13:27:35 CST