Convert sql statment from sql server 2005 to sql server 2000

It is only possible with either a temp table that is previously populated with sequential numbers or with a help of a WHILE loop. Which one do you prefer?

Chauu...can't the CTE be converted to a sub query?  Plus...on row 10 there seems to be an error where the first cte is calling itself.  I think the second cte beginning portion was deleted.

@lvbarnes: What you see here is a classic example of a recursive CTE. No, it can't be converted to a sub-query

Have a look at this SQLFiddle - http://sqlfiddle.com/#!3/2e9a0/1

declare @StartDate varchar(20), @EndDate varchar(20)
set @StartDate = '09/01/2013'
set @EndDate = '10/01/2013'

    select cast(@StartDate as datetime)-6 date_value 
    union
    select date_value + 1 
    from mycte    
    where date_value + 1 < cast(@EndDate as datetime)+7
select *  
from (
	select date_value 
	,datepart(dy, date_value) [day_of_year]
	,datename(dw, date_value) [day]
	,datepart(dw, date_value) [day_of_week]
	,datepart(dd, date_value) [day_of_month]
	,datepart(ww, date_value) [week]
	,datepart(mm, date_value) [month]
	,datename(mm, date_value) [month_name]
	,datepart(qq, date_value) [quarter]
	,datepart(yy, date_value) [year]
	,datepart(HH, date_value) [hour]
	,datepart(MI, date_value) [min]
	,datepart(SS, date_value) [sec]
	,datepart(MS, date_value) [millisecond]
	from mycte
)rq
where date_value > cast(@StartDate as datetime) - datepart(dw, cast(@StartDate as datetime))
OPTION (MAXRECURSION 0)

Select allOpen in new window

Problem is that SQL 2000 is quite old.

HTH,

Kent

For values between 0 and 255 we have a table already, so no need to create a temp one.
  select number from spt_values where type = 'p'
Integrating that into the original query:

declare @StartDate varchar(20), @EndDate varchar(20)
set @StartDate = '09/01/2013';
set @EndDate = '10/01/2013';

select *  
from (
  select date_value 
    ,datepart(dy, date_value) [day_of_year]
    ,datename(dw, date_value) [day]
    ,datepart(dw, date_value) [day_of_week]
    ,datepart(dd, date_value) [day_of_month]
    ,datepart(ww, date_value) [week]
    ,datepart(mm, date_value) [month]
    ,datename(mm, date_value) [month_name]
    ,datepart(qq, date_value) [quarter]
    ,datepart(yy, date_value) [year]
    ,datepart(HH, date_value) [hour]
    ,datepart(MI, date_value) [min]
    ,datepart(SS, date_value) [sec]
    ,datepart(MS, date_value) [millisecond]
  from (
    select date_value = cast(@StartDate as datetime)-6 + number
      from spt_values where type = 'p' and number < datediff(dy, @startdate, @enddate)+13
    ) mycte
) rq
where date_value > cast(@StartDate as datetime) - datepart(dw, cast(@StartDate as datetime))

Select allOpen in new window

@Qlemo: Agree about spt_values. However, if OP is stuck with Sql server 2000, he may as well create a permanent Numbers table. I have provided a link for reading

Brilliant... I love it.