Creating a date loop

[elder81]

Hello all,

I moving to another area of the comany I work in and I am learning SQL by being set challenges by my new manager.

The request in this case was to return a list of dates from today, 14 days into the future.

I have got this far,

USE [kdemo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[DAYDATELIST]
@date datetime, @days int
AS
BEGIN
SET NOCOUNT ON
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
but have an opne loop so the query never ends.

Any help would be greatly appreciated.

 

[gmmastros]

Take a close look at your loop.

[tt][blue]
WHILE @date2 < @anchordate
BEGIN
INSERT INTO #DATELIST (DTM) VALUES (@date)
SET @date2 = @date+1
END
[/blue][/tt]

@anchordate is set before the loop and never changes, so the loop is controlled by the @date2 variable. You are setting @date2 inside the loop, so you would expect the loop to eventually end. But, loop at what you are setting it to:

[tt]SET @date2 = [!]@date[/!]+1[/tt]

@date doesn't change within the loop, so it is always the same value. Furthermore, @date + 1 is less than @anchordate so the loop never ends.

I think you are making this more complicated than it has to be, but I'd rather not show you until after you fix this for yourself. I think my advice should be enough to get you started. After you fix this, post your corrected code and I will offer some further suggestions.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[elder81]

Thanks, I think I have fixed and appreciate you not "SHowing" me.

This is my fix: (It works but not sure if it is right or not)
USE [kdemo]
GO
/****** Object: StoredProcedure [dbo].[DAYDATELIST] Script Date: 07/02/2010 13:59:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[DAYDATELIST]
@date datetime, @days int
AS
BEGIN
SET NOCOUNT ON
DECLARE @anchordate DATETIME
SET @anchordate = @date + @days
CREATE TABLE #DATELIST (DTM DATETIME)
WHILE @date <= @anchordate
BEGIN
INSERT INTO #DATELIST (DTM) VALUES (@date)
SET @date = @date+1
END
SELECT CONVERT(VARCHAR (10),#DATELIST.DTM,103) AS DTM
FROM #DATELIST
END

 

[gmmastros]

I was going to mention about all those variables you created, most of which are not needed. This version looks a lot better than your previous one.

The only thing I don't like about this (and it's a relatively minor thing) is the way you are adding dates and integers. Specifically, here:

[tt]
SET @date = @date+1
[/tt]

SQL Server 2008 introduces a new data type called Date. If @date was a Date data type, your code would fail. Since @date is a parameter to the procedure, and you declare it as a DateTime type, there isn't any problem. But, if your code was using a "date" column from a table, and someone decides to change it from DateTime to Date, your code would suddenly fail. It's best to use the DateAdd function for ALL date math.

Set @date = DateAdd(Day, 1, @date)

DateAdd will continue to work, even with the new Date data type. This falls in to the category of "building good habits".

Personally, I know that I have a lot of code to change if I were to change my column data types for this very reason.

Also...

When you are writing queries, it's best to avoid loops if possible. I have a numbers table in every database I am responsible for. My numbers table is a single column with 1,000,000 rows. This table takes up approximately 10 megabytes in my database, but I use it for about 1,000,000 different things. With a numbers table, your code would run faster and wouldn't require any loops. For example...

Declare @days int
Declare @date DateTime

Set @days = 14
Set @date = '2010-07-02'

Select	DateAdd(Day, Num - 1, @date)
From	Numbers
Where	Num <= @Days
Order By Num

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[elder81]

Thanks for your reply, should have clarified, I am using SQL 2005 not 2008

 

[gmmastros]

My advice still wouldn't change. You might be using SQL2005 now, but what version of SQL server will you be using next year, or 5 years from now, or even 10 years. Writing code that might break in 10 years would require a very sophisticated crystal ball. But, this is an example where we don't need a crystal ball to predict the future.

All I'm saying is... it's best to change your habits now so that you can help to "future proof" your code.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom