Create list of dates, a month apart, starting from current date

Question

I'm looking for a simple select query (not using a table) to just return a list of dates, 1 month apart. The output should looke something like this, (assuming GetDate() = '2011-07-05 11:59:000' and I wanted between NOW() and NOW()+4 months

Date
2011-07-05 11:59:000
2011-08-05 11:59:000
2011-09-05 11:59:000
2011-10-05 11:59:000
2011-11-05 11:59:000

The part that's killing me is calculating the next year, for example if i run this query in Nov, the months should be listed as 11, 12, 1, 2. Thanks!

Ram · Answer 1 · 2011-07-05T22:16:11.747

8

You can use recursive CTE and need not string UNIONs together if the requirement is not fixed as below:

 ;with MonthlyCalendar as (
 select cast(getdate() as datetime) as dt
 union all
 select dateadd(mm, 1, dt)
 from MonthlyCalendar
) 
select top 5 dt as [Date] from MonthlyCalendar
option (maxrecursion 0)

When it comes to performance and you have the need for only 4 months above UNION is far superior than recursive option.

edited Jul 05 '11 at 22:16

answered Jul 05 '11 at 22:10

Ram

793
7
14

Your script is just fine, i was a bit worried because you had changed the maxrecursion. It is more or less the same. So I deleted mine and gave you credit. You dont need "order by" in this case. – t-clausen.dk Jul 06 '11 at 07:06
I wasnt sure as I read somewhere that whenever recursion is used to make sure of the order-by, however thanks for the clarification! – Ram Jul 06 '11 at 07:23

Jermaine · Answer 2 · 2014-05-07T14:40:29.447

I prefer to handle these small (one off) situations by looping through the data and building the list based on the current (or target) date:

if object_id('tempdb..#dates') is not null drop table #dates
select dateadd(MINUTE, -1, CONVERT(VARCHAR(10), dateadd(DD, 1, getdate()), 111)) result into #dates

declare @current datetime
select @current = result from #dates

while not exists (select * from #dates where result = dateadd(month, 4, @current))
  begin
    insert into #dates
    select dateadd(month, 1, max(result)) from #dates
  end

select * from #dates order by result

score 2 · Accepted Answer · answered Jul 05 '11 at 20:59

2

@JNK's answer, just reworked to give you each date in a row:

SELECT GETDATE() 'Date'
UNION
SELECT DATEADD(month, 1, GETDATE()) 'Date'
UNION
SELECT DATEADD(month, 2, GETDATE()) 'Date'
UNION
SELECT DATEADD(month, 3, GETDATE()) 'Date'
UNION
SELECT DATEADD(month, 4, GETDATE()) 'Date'

Had to do something like this just this morning!

answered Jul 05 '11 at 20:59

Ryan

26,884
9
56
83

Question is: Do you know of a shorter way to do this, other than a bunch of `UNION`s? – Ryan Jul 05 '11 at 21:03
Works perfect Ryan, thanks! A short way would be nice, but this works. Thanks! – russds Jul 05 '11 at 21:30
1

@Russ You could use a recursive CTE for this as well. – JNK Jul 05 '11 at 21:52

score 1 · Answer 4 · answered Jul 05 '11 at 20:56

1

SELECT GETDATE(),
       DATEADD(month, 1, GETDATE()),    
       DATEADD(month, 2, GETDATE()),
       DATEADD(month, 3, GETDATE()),
       DATEADD(month, 4, GETDATE())

DATEADD takes care of all that year consideration logic for you, and leap years and such too.

Obviously this returns a list of columns. See Ryan's answer for the row solution!

answered Jul 05 '11 at 20:56

JNK

63,321
15
122
138

+1 but quickly gets unwieldy if you want some larger number of dates. – Matthew Jul 05 '11 at 20:59

score 1 · Answer 5 · answered Jul 05 '11 at 21:03

1

try this :

DECLARE @intFlag INT
declare @LastLimit as int
set @LastLimit = 4
SET @intFlag = 0
WHILE (@intFlag <@LastLimit)
BEGIN
select DATEADD(month, @intFlag, GETDATE())  
SET @intFlag = @intFlag + 1
END

answered Jul 05 '11 at 21:03

Ovais Khatri

3,201
16
14

Giuseppe · Answer 6 · 2019-02-08T11:29:47.077

In SQL Oracle, you can easily create a list of dates using CONNECT BY. For example, if you want all the months between '2000-12-31' and today:

select add_months(date '2000-12-31',level) dates
from dual 
connect by level <=  months_between(sysdate, date '2000-12-31');

The function used to obtain the number of months, here months_between, can change between different SQL versions (e.g. in SQL Server it should be datediff()).

score 0 · Answer 7 · answered Jul 05 '11 at 20:59

0

You can use a dynamic script to build a calendar set.
A good example can be found here:
http://blog.namwarrizvi.com/?p=139

In that example you would just replaced the DATEADD and DATEDIFF to use months instead of days.

answered Jul 05 '11 at 20:59

Matthew

10,244
5
49
104

Please note the first sentence of the OP: I'm looking for a simple select query **(not using a table)** – JNK Jul 05 '11 at 21:00
Note that this does not `INSERT` into anything, "calendar table" is just common phrasing for what he's after. I'll edit my answer to say "Calendar set" because, without a corresponding `INSERT` that's precisely what this is. – Matthew Jul 05 '11 at 21:03

score 0 · Answer 8 · edited May 23 '17 at 12:08

0

There is a generic elegant solution on the problem here: Get usernames logged in on day by day basis from database

Of course, it will require adjustments, but the principle is great.

edited May 23 '17 at 12:08

Community

1
1

answered Jul 05 '11 at 21:01

Adriano Carneiro

57,693
12
90
123

score -1 · Answer 9 · answered Jul 05 '11 at 22:08

It's often useful to keep a table of incrementing values, as large as you need it to be:

create table sequence ( value int not null primary key clustered )
insert sequence values(0)
insert sequence values(1)
insert sequence values(2)
insert sequence values(3)
. . .
insert sequence values(n)

With such a table, producing a list of any size is trivial. This will give you 36 date/time values a month apart, starting with the current date/time.

select top 36
       dtValue = dateadd( month , sequence.value , date(current_timestamp) )
from dbo.sequence
order by sequence.value

Create list of dates, a month apart, starting from current date

9 Answers9