Re: Last Week

"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.

I did run into the end of the year problem with the type of query you provided in the second case when implemented in Access.

Once again thanks for helping.

Cheers;

Bill Received on Mon Jan 27 2003 - 17:06:58 CST