Member Avatar for cheapterp

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)

Edited by cheapterp because: n/a
  • 2 Contributors
  • 2 Replies
  • 235 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by cheapterp
Member Avatar for sknake 
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:

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)
Member Avatar for cheapterp 
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!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.