Convert mysql query to sqlserver query

Can someone help me convert this sql action from MySQL to something that will work for Sql Server 2000.

Thanks


USE test;
DROP TABLE IF EXISTS _t_helper;
CREATE TABLE _t_helper (id TINYINT UNSIGNED NOT NULL PRIMARY KEY);
INSERT INTO _t_helper (id) VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);

DROP TABLE IF EXISTS calendar_helper;
CREATE TABLE calendar_helper (
dt DATE NOT NULL PRIMARY KEY,
yr MEDIUMINT UNSIGNED NOT NULL,
mon TINYINT UNSIGNED NOT NULL,
dom TINYINT UNSIGNED NOT NULL,
lbl CHAR(15) NOT NULL,
KEY(yr, mon, dom)
);

SET @counter := -1;
SET @from := '2000-01-01';
SET @to := '2010-12-31';
SET @thedate := @from;

INSERT INTO calendar_helper
SELECT
@thedate := (@from + INTERVAL @counter := @counter + 1 DAY),
YEAR(@thedate),
MONTH(@thedate),
DAY(@thedate),
DATE_FORMAT(@thedate, "%M %e")
FROM _t_helper t1, _t_helper t2, _t_helper t3, _t_helper t4 -- cartesian products worth 10x10x10x10 = 10000 rows
WHERE
@thedate < @to;
LVL 11
Robb HillSenior .Net DeveloperAsked:
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.

fanopoeCommented:
I think this will do what you want:

Use Test

DROP TABLE calendar_helper;

CREATE TABLE calendar_helper (
dt  datetime NOT NULL PRIMARY KEY,
yr INT NOT NULL,
mon TINYINT NOT NULL,
dom TINYINT NOT NULL,
lbl CHAR(15) NOT NULL
);

truncate table calendar_helper

begin
      declare @thedate datetime
      declare @lbl varchar(23)
      select @thedate = '2000-01-01'

      while datediff(d, @thedate, '2010-12-31') > 0
      begin
            select @lbl = datename(Month, @thedate)  + ' ' + cast(day(@thedate) as varchar)
            INSERT INTO calendar_helper
            select @thedate, YEAR(@thedate), MONTH(@thedate), DAY(@thedate), @lbl;
            select @thedate = dateadd(d, 1, @thedate);
      end
end
go

select * from calendar_helper



it gives me the follwing results:
2000-01-01 00:00:00.000      2000      1      1      January 1      
2000-01-02 00:00:00.000      2000      1      2      January 2      
etc...


HTH



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
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.