how to display weekdays from below two days (Monday and Friday)?

how to display weekdays from below two days (Monday and Friday) Using SQL QUERY?

Date_From= 01/03/2011
Date_To =15/03/2011

I need output like below

Date_From   Date_To    
01/03/2011  01/03/2011
04/03/2011  04/03/2011  'Friday
05/03/201开发者_开发技巧1  06/03/2011
07/03/2011  07/03/2011  'Monday
08/03/2011  10/03/2011
11/03/2011  11/03/2011  'Friday
12/03/2011  13/03/2011
14/03/2011  14/03/2011  'Monday
15/03/2011  15/03/2011

hoping your help

select 
      t1.Date_From,  
      t1.Date_To, 
      case when DATENAME(dw,t1.Date_to) IN ('friday','monday') THEN DATENAME(dw,t1.Date_to) else NULL END    
from table t1

This is using Oracle. I can get the 15th to show up if I use Oracle analytic functions, but I don't know of a portable way to get the result in one row to depend on the existence of other rows. Also, due to the Cartesian product, do not use this without limiting it to a small number of rows.

select to_char(d1.d, 'DD/MM/YYYY')
, to_char(d2.d, 'DD/MM/YYYY')
, decode(d1.n, 'MONDAY', '''Monday', 'FRIDAY', '''Friday')
from (select to_date('2011-02-28') + rownum as d
    , to_char(to_date('2011-02-28') + rownum, 'FMDAY') as n
    from user_objects where rownum <= 15) d1
, (select to_date('2011-02-28') + rownum as d
    , to_char(to_date('2011-02-28') + rownum, 'FMDAY') as n
    from user_objects where rownum <= 15) d2
where d1.d <= d2.d
and d2.d - d1.d < 7
and ((d1.d = d2.d and d1.n in ('MONDAY', 'FRIDAY'))
    or (d1.n = 'TUESDAY' and d2.n = 'THURSDAY')
    or (d1.n = 'SATURDAY' and d2.n = 'SUNDAY'))
order by d1.d, d2.d;

This is for SQL Server 2005+.

DECLARE @Date_From datetime, @Date_To datetime;
SET @Date_From = '20110301';
SET @Date_To = '20110315';

WITH datelist AS (
  SELECT
    Date = @Date_From,
    GroupID = 1 
  UNION ALL
  SELECT
    Date = DATEADD(day, 1, Date),
    GroupID = CASE
      WHEN DATENAME(dw, Date) IN ('Sunday', 'Monday', 'Thursday', 'Friday')
      THEN 1
      ELSE 0
    END + GroupID
  FROM datelist
  WHERE Date < @Date_To
)
SELECT
  Date_From = MIN(Date),
  Date_To = MAX(Date)
FROM datelist
GROUP BY GroupID

Output:

Date_From               Date_To
----------------------- -----------------------
2011-03-01 00:00:00.000 2011-03-03 00:00:00.000
2011-03-04 00:00:00.000 2011-03-04 00:00:00.000
2011-03-05 00:00:00.000 2011-03-06 00:00:00.000
2011-03-07 00:00:00.000 2011-03-07 00:00:00.000
2011-03-08 00:00:00.000 2011-03-10 00:00:00.000
2011-03-11 00:00:00.000 2011-03-11 00:00:00.000
2011-03-12 00:00:00.000 2011-03-13 00:00:00.000
2011-03-14 00:00:00.000 2011-03-14 00:00:00.000
2011-03-15 00:00:00.000 2011-03-15 00:00:00.000