Oracle Database - How to retrieve the day names in period in oracle

Asked By Sherif on 05-Jan-11 02:14 AM
In Oracle Reports, i need to display the name of adays in whole the period between two dates give as parameters.
like P_Start_Date := 15-05-2010
   P_End_Date  := 22-05-2010
 
Day            Date
Sunday         15-05-2010
Monday        16-05-2010
Tuesday          17-05-2010
Wedensday       18-05-2010
Thursday         19-05-2010
Friday          20-05-2010
Saturday        21-05-2010
Sunday           22-05-2010

Date Difference

Lalitha Kumaran replied to Sherif on 05-Jan-11 03:43 AM

Hope this query will hep you.


select * from dummytbl where
cdate between to_date('15-5-2010','dd-mm-yyyy') and to_date('22-05-2010','dd-mm-yyyy')

Query to select only Days column

Lalitha Kumaran replied to Lalitha Kumaran on 05-Jan-11 03:52 AM

select days from dummytbl where
cdate between to_date('15/5/2010','dd/mm/yyyy') and to_date('22/5/2010','dd/mm/yyyy')

Sherif replied to Lalitha Kumaran on 05-Jan-11 04:35 AM
First of All,

Thanks for your replay, but actually i found another query gives me my expectation.

declare 
     begin_date date := to_date ('01-12-2010','dd-mm-yyyy');
     stop_date date  := to_date ('11-12-2010','dd-mm-yyyy');
  end_date  date;
  d_date    date;
  dayname   Varchar2(255);
 begin
select begin_date , to_char(begin_date,'DAY') into d_date , dayname from dual;
dbms_output.put_line(d_date || ' - ' || dayname);
end_date := begin_date + 1;
   while stop_date >= end_date 
 loop
  select end_date , to_char(end_date,'DAY') into d_date , dayname from dual;
  dbms_output.put_line(d_date || ' - ' || dayname);
  end_date :=  end_date + 1;
 end loop;
 end;
/
Ashfaq Mohammad replied to Sherif on 24-Jan-11 05:24 AM
This will fulfill your requirement exactly.
SELECT TO_DATE('30-nov-2010') + level - 1 dt, TO_Char(To_Date('30-nov-2010') + level - 1,'Day') day FROM dual CONNECT BY level <=(TO_DATE('14-Dec-2010')-TO_DATE('30-nov-2010'));

Here: '14-Dec-2010' is EndDate and '30-nov-2010' is StartDate.
DT              DAY    
------------------------- ---------
30-NOV-10    Tuesday  
01-DEC-10   Wednesday
02-DEC-10   Thursday 
03-DEC-10   Friday   
04-DEC-10   Saturday 
05-DEC-10   Sunday   
06-DEC-10   Monday   
07-DEC-10   Tuesday  
08-DEC-10   Wednesday
09-DEC-10   Thursday 
10-DEC-10   Friday   
11-DEC-10   Saturday 
12-DEC-10   Sunday   
13-DEC-10   Monday
Sherif replied to Ashfaq Mohammad on 24-Jan-11 05:29 AM
Thanks Mr.Ashfaq Mohammad to your reply.

Actually i appreciated it.


Thanks again.