Solved

Convert mysql query to sqlserver query

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

708 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

11 Experts available now in Live!

Get 1:1 Help Now