Solved

Convert mysql query to sqlserver query

Posted on 2007-03-30
1
188 Views
Last Modified: 2007-03-30
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;
0
Comment
Question by:Robb Hill
1 Comment
 
LVL 11

Accepted Solution

by:
fanopoe earned 500 total points
ID: 18826407
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

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now