Capture Previous Week

[mooks]

Hi

Is there any function in Oracle that will helps to capture previous week from Mon-Sun?

Thanks!

[slimdave]

how about ...

where my_date between NEXT_DAY(sysdate-1,'MONDAY')-7
and NEXT_DAY(sysdate-1,'SUNDAY')-7

[mooks]

Thanks .. but i cant seems to be able to get it working :(

For example, today date is 06 May 2003, therefore I would like to retrieve data from previous week which is 28 Apr 2003 (Mon) to 04 May 2003 (Sun)... any help please??

[tamilselvan]

Try this:
17:31:36 H8STRW3>select * from t1 ;

PDATE
---------
21-APR-03
22-APR-03
23-APR-03
24-APR-03
25-APR-03
26-APR-03
27-APR-03
28-APR-03
29-APR-03
30-APR-03
01-MAY-03
02-MAY-03
03-MAY-03
04-MAY-03
05-MAY-03
06-MAY-03

16 rows selected.

Now run the query :
select pdate from t1
where trunc(pdate) >= (
select trunc(case
when substr(to_char(sysdate-6,'DAY'),1,3) = 'MON' then (sysdate-6)
when substr(to_char(sysdate-7,'DAY'),1,3) = 'MON' then (sysdate-7)
when substr(to_char(sysdate-8,'DAY'),1,3) = 'MON' then (sysdate-8)
when substr(to_char(sysdate-9,'DAY'),1,3) = 'MON' then (sysdate-9)
when substr(to_char(sysdate-10,'DAY'),1,3) = 'MON' then (sysdate-10)
when substr(to_char(sysdate-11,'DAY'),1,3) = 'MON' then (sysdate-11)
when substr(to_char(sysdate-12,'DAY'),1,3) = 'MON' then (sysdate-12)
when substr(to_char(sysdate-13,'DAY'),1,3) = 'MON' then (sysdate-13)
when substr(to_char(sysdate-14,'DAY'),1,3) = 'MON' then (sysdate-14)
end) tdate1
from dual )
and trunc(pdate) <= (
select trunc(case
when substr(to_char(sysdate-1,'DAY'),1,3) = 'SUN' then (sysdate-1)
when substr(to_char(sysdate-2,'DAY'),1,3) = 'SUN' then (sysdate-2)
when substr(to_char(sysdate-3,'DAY'),1,3) = 'SUN' then (sysdate-3)
when substr(to_char(sysdate-4,'DAY'),1,3) = 'SUN' then (sysdate-4)
when substr(to_char(sysdate-5,'DAY'),1,3) = 'SUN' then (sysdate-5)
when substr(to_char(sysdate-6,'DAY'),1,3) = 'SUN' then (sysdate-6)
when substr(to_char(sysdate-7,'DAY'),1,3) = 'SUN' then (sysdate-7)
end ) tdate2 from dual )
/

PDATE
---------
28-APR-03
29-APR-03
30-APR-03
01-MAY-03
02-MAY-03
03-MAY-03
04-MAY-03

7 rows selected.

Elapsed: 00:00:00.20

Now add one more row:
insert into t1 values (to_date('28-apr-2003','dd-mon-yyyy'))
commit;
run the query now:
17:34:38 H8STRW3>@w1

PDATE
---------
28-APR-03
29-APR-03
30-APR-03
01-MAY-03
02-MAY-03
03-MAY-03
04-MAY-03
28-APR-03

8 rows selected.

Elapsed: 00:00:00.00
17:34:41 H8STRW3>

I hope this will help you .

Tamilselvan

[jmodic]

SELECT NEXT_DAY(TRUNC(SYSDATE),'MONDAY')-7-ROWNUM
FROM ALL_OBJECTS
WHERE ROWNUM <= 7;

[mooks]

Thanks for all contributions!