MS SQL

Loop to generate future dates?!

Please support our MS SQL advertiser: Intel Parallel Studio Home

Thread Solved

•

Join Date: Jun 2008

Posts: 28

Reputation: cheapterp is an unknown quantity at this point

Solved Threads: 2

cheapterp

Offline

Light Poster

Loop to generate future dates?!

Oct 26th, 2009

What is the best way to generate a set of dates based on today's date? Essentially, what I wanna do is:
1. Get Current Date
2. Populate a table with future dates 6 months apart and go up until I reach, let's say, 12/31/2020

I have looked at some of the date functions offered by MS-SQL 2005, but not quite sure how to run a loop. (I use ColdFusion as the front-end)

Last edited by cheapterp; Oct 26th, 2009 at 1:23 pm.

•

Join Date: Feb 2009

Posts: 3,346

Reputation: sknake has much to be proud of

Solved Threads: 603

sknake

Offline

.NET Enthusiast

Oct 27th, 2009

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
IF OBJECT_ID('tempdb..#DateTable', 'U') IS NOT NULL DROP TABLE #DateTable
CREATE TABLE #DateTable
(
  [Date] DateTime
)
 
DECLARE @Begin DateTime, @End DateTime, @dt DateTime
 
SET @Begin = Cast(Floor(Cast(GetDate() AS float)) AS datetime)
SET @End = Cast('12/31/2020' AS DateTime)
SET @dt = @Begin
 
SET NOCOUNT ON
WHILE (@dt <= @End)
BEGIN
  INSERT INTO #DateTable ([Date]) Values (@dt)
  SET @dt = DateAdd(month, 6, @dt)
END
SET NOCOUNT OFF
 
SELECT * FROM #DateTable
IF OBJECT_ID('tempdb..#DateTable', 'U') IS NOT NULL DROP TABLE #DateTable
Create Table #DateTable
(
  [Date] DateTime
)

DECLARE @Begin DateTime, @End DateTime, @dt DateTime

Set @Begin = Cast(Floor(Cast(GetDate() as float)) as datetime)
Set @End = Cast('12/31/2020' as DateTime)
Set @dt = @Begin

SET NOCOUNT ON
WHILE (@dt <= @End)
BEGIN
  Insert Into #DateTable ([Date]) Values (@dt)
  Set @dt = DateAdd(month, 6, @dt)
END
SET NOCOUNT OFF

Select * From #DateTable

Results:

Help with Code Tags

text Syntax (Toggle Plain Text)

Date
-----------------------
2009-10-27 00:00:00.000
2010-04-27 00:00:00.000
2010-10-27 00:00:00.000
2011-04-27 00:00:00.000
2011-10-27 00:00:00.000
2012-04-27 00:00:00.000
2012-10-27 00:00:00.000
2013-04-27 00:00:00.000
2013-10-27 00:00:00.000
2014-04-27 00:00:00.000
2014-10-27 00:00:00.000
2015-04-27 00:00:00.000
2015-10-27 00:00:00.000
2016-04-27 00:00:00.000
2016-10-27 00:00:00.000
2017-04-27 00:00:00.000
2017-10-27 00:00:00.000
2018-04-27 00:00:00.000
2018-10-27 00:00:00.000
2019-04-27 00:00:00.000
2019-10-27 00:00:00.000
2020-04-27 00:00:00.000
2020-10-27 00:00:00.000
 
(23 row(s) affected)

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Jun 2008

Posts: 28

Reputation: cheapterp is an unknown quantity at this point

Solved Threads: 2

cheapterp

Offline

Light Poster

Oct 27th, 2009

•

Originally Posted by sknake

Help with Code Tags

MS SQL Syntax (Toggle Plain Text)
IF OBJECT_ID('tempdb..#DateTable', 'U') IS NOT NULL DROP TABLE #DateTable
CREATE TABLE #DateTable
(
  [Date] DateTime
)
......
......
......
IF OBJECT_ID('tempdb..#DateTable', 'U') IS NOT NULL DROP TABLE #DateTable Create Table #DateTable ( [Date] DateTime ) ...... ...... ......

Thanks Scott, that was just what I was looking for. This was my first time working with loops in MS-SQL - very useful!