Hi Friends,
How to extract all Sat,Sun in a year,
Can any any suggest in this...It's very urgent
All saturday and Sundays in a year....
-
- Posts: 40
- Joined: Sat Mar 01, 2008 3:39 pm
- Location: India
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;
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;
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
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
-----------------------------------------------------------------------
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
Who is online
Users browsing this forum: No registered users and 1 guest