ERPstuff - Oracle EBS Community

Hi Friends,

How to extract all Sat,Sun in a year,
Can any any suggest in this...It's very urgent

Try this , I hope this will heip you.

declare
vdaycnt number;
vday varchar2(20);

begin

go_block('block3');

for vdaycnt in 1..365
loop

select rtrim(to_char(to_date('01-Jan-2008')+vdaycnt,'DAY')) into vday from dual;

if vday in ('SATURDAY','SUNDAY') then
:sat_sun:=vday;
:dat:=to_date('01-Jan-2008')+vdaycnt;
next_record;
end if;

end loop;
end;

http://asktom.oracle.com/pls/asktom/f?p ... 5012348071

http://asktom.oracle.com/pls/asktom/f?p ... 0404471394

SELECT SUM(TOT_SUNDAY) TOT_SUNDAY,
SUM(TOT_SATURDAY) TOT_SATURDAY
FROM (
-----------------
SELECT COUNT(*) TOT_SUNDAY,0 AS TOT_SATURDAY FROM (
SELECT UPPER(TRIM(TO_CHAR(TO_DATE(:FDATE)+ROWNUM-1,'DAY'))) D
FROM ALL_OBJECTS WHERE TO_DATE(:FDATE)+ROWNUM-1 BETWEEN
TO_DATE(:FDATE) AND TO_DATE(:TDATE))
WHERE D='SUNDAY'

UNION ALL

SELECT 0 AS TOT_SUNDAY,COUNT(*) TOT_SATURDAY FROM (
SELECT UPPER(TRIM(TO_CHAR(TO_DATE(:FDATE)+ROWNUM-1,'DAY'))) D
FROM ALL_OBJECTS WHERE TO_DATE(:FDATE)+ROWNUM-1 BETWEEN
TO_DATE(:FDATE) AND TO_DATE(:TDATE))
WHERE D='SATURDAY'
------------------
)

Write above code as it is in Toad or SQL Developer and only provide two parameters:
Fdate---from date and
Tdate...to date.

It gave all saturday and sunday between two dates.

I think it solve ur problem.

Thanks,
Farooq

IF YOU WANT TO EXTRACT DATES OF ALL SATURDAYS AND SUNDAYS BETWEEN TWO DATES THEN EXECUTE THE FOLLOWING QUERY TO FIND RESULTS:
-----------------------------------------------------------------------
SELECT D DAY,DT DATE1 FROM (
SELECT UPPER(TRIM(TO_CHAR(TO_DATE(:FDATE)+ROWNUM-1,'DAY'))) D,TO_DATE(:FDATE)+ROWNUM-1 AS DT
FROM ALL_OBJECTS WHERE TO_DATE(:FDATE)+ROWNUM-1 BETWEEN
TO_DATE(:FDATE) AND TO_DATE(:TDATE))
WHERE D IN ('SUNDAY','SATURDAY')
----------------------------------------------------------------------

HOPE U FIND THE SOLUTION.

FOR FURTHER DETAIL PLZ CONTACT WITH ME.

THANKS,
FAROOQ