Re: Last Week

Originally posted by Bill
> "Jim Kennedy" wrote
>
> > select ... from ... where
> > the_date_field >= trunc((sysdate-7)) and
> > the_date_field
> > 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( 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

You can try

SELECT.....
FROM....
WHERE <the_date_fld> BETWEEN (SYSDATE - (TO_CHAR(SYSDATE, 'D') + 6)) AND (SYSDATE - (TO_CHAR(SYSDATE, 'D')))

• rajXesh

--
All you need in this life is ignorance and confidence, and then success is sure. -- Mark Twain (1835 - 1910)


Posted via http://dbforums.com