Solved

Convert mysql query to sqlserver query

Posted on 2007-03-30
1
191 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 to SQL Server 2016 24 57
Creating a View from a CTE 15 50
DMV Script to find how many times statistics are utilized 2 30
t-sql left join 2 34
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

752 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