Hi
Is there any function in Oracle that will helps to capture previous week from Mon-Sun? :confused:
Thanks!
Printable View
Hi
Is there any function in Oracle that will helps to capture previous week from Mon-Sun? :confused:
Thanks!
how about ...
where my_date between NEXT_DAY(sysdate-1,'MONDAY')-7
and NEXT_DAY(sysdate-1,'SUNDAY')-7
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??:confused:
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
SELECT NEXT_DAY(TRUNC(SYSDATE),'MONDAY')-7-ROWNUM
FROM ALL_OBJECTS
WHERE ROWNUM <= 7;
Thanks for all contributions! :cool: