Solved

Convert mysql query to sqlserver query

Posted on 2007-03-30
1
192 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

Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

630 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