Loop Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Loop Query

Author

Topic

Elder81
Starting Member

2 Posts

Posted - 2010-07-02 : 06:09:59

Hi all,

I am new to SQL and my boss set me a challenge to create a list of dates between different dates.

It is created as a stroed procedure.

I have created the following SQL script but am getting errors:

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE 14DAYDATELIST
-- Add the parameters for the stored procedure here
@date datetime, @days int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

-- Insert statements for procedure here

DECLARE @anchordate DATETIME, @date2 datetime
SET @date2 = @date
SET @anchordate = @date2 + @days
CREATE TABLE #DATELIST (DTM DATETIME)
WHILE @date2 < @anchordate
BEGIN
INSERT INTO #DATELIST (DTM) VALUES (@date)
SET @date2 = @date+1
END

END

These are the errors I am getting!

ANy ideas and help would be appreciatted!

Thanks

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-07-02 : 06:21:41

Try this script,

declare @startdate datetime
declare @enddate datetime
set @startdate =getdate()
set @enddate =dateadd(d,6,getdate())
with DateList as
(
select @startdate DateValue
union all
select DateValue + 1
from DateList
where DateValue + 1 < @enddate
)
select DateValue
from DateList

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

Elder81
Starting Member

2 Posts

Posted - 2010-07-02 : 06:27:39

Thanks for the reply. I shall use that one, but can you see any reason why mine did not work?

My boss showed me this way to start with!

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-07-05 : 00:41:23

Check this for your query to work,

DECLARE @anchordate DATETIME
DECLARE @date2 datetime
DECLARE @date datetime
DECLARE @days INT
SET @days=2
SET @date = GETDATE()

SET @date2 = @date
SET @anchordate = DATEADD(d,@days,@date2)

CREATE TABLE #DATELIST (DTM DATETIME)
WHILE @date2 < @anchordate
BEGIN
INSERT INTO #DATELIST (DTM) VALUES (@date2)
SET @date2 = DATEADD(d,1,@date2)
END
SELECT * FROM #DATELIST
DROP TABLE #DATELIST

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-05 : 02:04:37

[code]
DECLARE @anchordate DATETIME, @date2 datetime
SET @date2 = @date
SET @anchordate = @date2 + @days
CREATE TABLE #DATELIST (DTM DATETIME)
WHILE @date2 < @anchordate
BEGIN
INSERT INTO #DATELIST (DTM) VALUES (@date)
SET @date2 = @date+1
END
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources