Convert sql statment from sql server 2005 to sql server 2000

Can someone convert this sql statment from sql server 2005 to sql server 2000 compatable statment.

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

with mycte as
    (
    select cast(@StartDate as datetime)-6 date_value 
    union all
    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)

Open in new window

quest_capitalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
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?
0
Lawrence BarnesCommented:
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.
0
chaauCommented:
@lvbarnes: What you see here is a classic example of a recursive CTE. No, it can't be converted to a sub-query
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Kent DyerIT Security Analyst SeniorCommented:
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)

Open in new window


Problem is that SQL 2000 is quite old.

HTH,

Kent
0
chaauCommented:
Read here about Numbers table
This is how I'd do it with a help of a temp "numbers" table (SQLFiddle):

create table #numbers 
(n int not null primary key)
declare @i int
set @i = 0
while @i < 100
begin
 insert into #numbers values(@i)
 set @i = @i + 1
end

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 cast(@StartDate as datetime)-6 + n AS date_value
     FROM #numbers
     WHERE cast(@EndDate as datetime)+7 > cast(@StartDate as datetime)-6 + n
  ) as mycte
)rq
where date_value > cast(@StartDate as datetime) - datepart(dw, cast(@StartDate as datetime))

Open in new window


And here is the output:
|                       DATE_VALUE | DAY_OF_YEAR |       DAY | DAY_OF_WEEK | DAY_OF_MONTH | WEEK | MONTH | MONTH_NAME | QUARTER | YEAR | HOUR | MIN | SEC | MILLISECOND |
------------------------------------|----------------|--------------|----------------|-----------------|---------|----------|---------------|------------|---------|---------|--------|--------|----------------|--
| September, 01 2013 00:00:00+0000 |         244 |    Sunday |           1 |            1 |   36 |     9 |  September |       3 | 2013 |    0 |   0 |   0 |           0 |
| September, 02 2013 00:00:00+0000 |         245 |    Monday |           2 |            2 |   36 |     9 |  September |       3 | 2013 |    0 |   0 |   0 |           0 |
| September, 03 2013 00:00:00+0000 |         246 |   Tuesday |           3 |            3 |   36 |     9 |  September |       3 | 2013 |    0 |   0 |   0 |           0 |
| September, 04 2013 00:00:00+0000 |         247 | Wednesday |           4 |            4 |   36 |     9 |  September |       3 | 2013 |    0 |   0 |   0 |           0 |
| September, 05 2013 00:00:00+0000 |         248 |  Thursday |           5 |            5 |   36 |     9 |  September |       3 | 2013 |    0 |   0 |   0 |           0 |
| September, 06 2013 00:00:00+0000 |         249 |    Friday |           6 |            6 |   36 |     9 |  September |       3 | 2013 |    0 |   0 |   0 |           0 |
| September, 07 2013 00:00:00+0000 |         250 |  Saturday |           7 |            7 |   36 |     9 |  September |       3 | 2013 |    0 |   0 |   0 |           0 |
| September, 08 2013 00:00:00+0000 |         251 |    Sunday |           1 |            8 |   37 |     9 |  September |       3 | 2013 |    0 |   0 |   0 |           0 |
| September, 09 2013 00:00:00+0000 |         252 |    Monday |           2 |            9 |   37 |     9 |  September |       3 | 2013 |    0 |   0 |   0 |           0 |
| September, 10 2013 00:00:00+0000 |         253 |   Tuesday |           3 |           10 |   37 |     9 |  September |       3 | 2013 |    0 |   0 |   0 |           0 |
| September, 11 2013 00:00:00+0000 |         254 | Wednesday |           4 |           11 |   37 |     9 |  September |       3 | 2013 |    0 |   0 |   0 |           0 |
| September, 12 2013 00:00:00+0000 |         255 |  Thursday |           5 |           12 |   37 |     9 |  September |       3 | 2013 |    0 |   0 |   0 |           0 |
| September, 13 2013 00:00:00+0000 |         256 |    Friday |           6 |           13 |   37 |     9 |  September |       3 | 2013 |    0 |   0 |   0 |           0 |
| September, 14 2013 00:00:00+0000 |         257 |  Saturday |           7 |           14 |   37 |     9 |  September |       3 | 2013 |    0 |   0 |   0 |           0 |
| September, 15 2013 00:00:00+0000 |         258 |    Sunday |           1 |           15 |   38 |     9 |  September |       3 | 2013 |    0 |   0 |   0 |           0 |
| September, 16 2013 00:00:00+0000 |         259 |    Monday |           2 |           16 |   38 |     9 |  September |       3 | 2013 |    0 |   0 |   0 |           0 |
| September, 17 2013 00:00:00+0000 |         260 |   Tuesday |           3 |           17 |   38 |     9 |  September |       3 | 2013 |    0 |   0 |   0 |           0 |
| September, 18 2013 00:00:00+0000 |         261 | Wednesday |           4 |           18 |   38 |     9 |  September |       3 | 2013 |    0 |   0 |   0 |           0 |
| September, 19 2013 00:00:00+0000 |         262 |  Thursday |           5 |           19 |   38 |     9 |  September |       3 | 2013 |    0 |   0 |   0 |           0 |
| September, 20 2013 00:00:00+0000 |         263 |    Friday |           6 |           20 |   38 |     9 |  September |       3 | 2013 |    0 |   0 |   0 |           0 |
| September, 21 2013 00:00:00+0000 |         264 |  Saturday |           7 |           21 |   38 |     9 |  September |       3 | 2013 |    0 |   0 |   0 |           0 |
| September, 22 2013 00:00:00+0000 |         265 |    Sunday |           1 |           22 |   39 |     9 |  September |       3 | 2013 |    0 |   0 |   0 |           0 |
| September, 23 2013 00:00:00+0000 |         266 |    Monday |           2 |           23 |   39 |     9 |  September |       3 | 2013 |    0 |   0 |   0 |           0 |
| September, 24 2013 00:00:00+0000 |         267 |   Tuesday |           3 |           24 |   39 |     9 |  September |       3 | 2013 |    0 |   0 |   0 |           0 |
| September, 25 2013 00:00:00+0000 |         268 | Wednesday |           4 |           25 |   39 |     9 |  September |       3 | 2013 |    0 |   0 |   0 |           0 |
| September, 26 2013 00:00:00+0000 |         269 |  Thursday |           5 |           26 |   39 |     9 |  September |       3 | 2013 |    0 |   0 |   0 |           0 |
| September, 27 2013 00:00:00+0000 |         270 |    Friday |           6 |           27 |   39 |     9 |  September |       3 | 2013 |    0 |   0 |   0 |           0 |
| September, 28 2013 00:00:00+0000 |         271 |  Saturday |           7 |           28 |   39 |     9 |  September |       3 | 2013 |    0 |   0 |   0 |           0 |
| September, 29 2013 00:00:00+0000 |         272 |    Sunday |           1 |           29 |   40 |     9 |  September |       3 | 2013 |    0 |   0 |   0 |           0 |
| September, 30 2013 00:00:00+0000 |         273 |    Monday |           2 |           30 |   40 |     9 |  September |       3 | 2013 |    0 |   0 |   0 |           0 |
|   October, 01 2013 00:00:00+0000 |         274 |   Tuesday |           3 |            1 |   40 |    10 |    October |       4 | 2013 |    0 |   0 |   0 |           0 |
|   October, 02 2013 00:00:00+0000 |         275 | Wednesday |           4 |            2 |   40 |    10 |    October |       4 | 2013 |    0 |   0 |   0 |           0 |
|   October, 03 2013 00:00:00+0000 |         276 |  Thursday |           5 |            3 |   40 |    10 |    October |       4 | 2013 |    0 |   0 |   0 |           0 |
|   October, 04 2013 00:00:00+0000 |         277 |    Friday |           6 |            4 |   40 |    10 |    October |       4 | 2013 |    0 |   0 |   0 |           0 |
|   October, 05 2013 00:00:00+0000 |         278 |  Saturday |           7 |            5 |   40 |    10 |    October |       4 | 2013 |    0 |   0 |   0 |           0 |
|   October, 06 2013 00:00:00+0000 |         279 |    Sunday |           1 |            6 |   41 |    10 |    October |       4 | 2013 |    0 |   0 |   0 |           0 |
|   October, 07 2013 00:00:00+0000 |         280 |    Monday |           2 |            7 |   41 |    10 |    October |       4 | 2013 |    0 |   0 |   0 |           0 |

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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))

Open in new window

0
chaauCommented:
@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
0
quest_capitalAuthor Commented:
Brilliant... I love it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.