How to get all dates between current month and the two last months

Question

I am trying to get all dates existing between the current month and the two last months.

For example: today 10-01-2019 With an sql script, I will get all dates between 2018-10-01 and 2019-01-31.

with cte as
  (
  select getdate() as   n
  union all
  select  dateadd(DAY,-1,n) from cte where month(dateadd(dd,-1,n)) < month(DATEADD(month, -3, getdate())) --and month(DATEADD(month, 0, getdate()))
   union all
  select  dateadd(DAY,-1,n) from cte where month(dateadd(dd,-1,n)) > month(DATEADD(month, 0, getdate()))
  )
  select * from cte

I get

error Msg 530, Level 16, State 1, Line 1 The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Possible duplicate of [Get all dates between two dates in SQL Server](https://stackoverflow.com/questions/23290454/get-all-dates-between-two-dates-in-sql-server) — DavidG, Jan 10 '19 at 14:47

Sean Lange · Answer 1 · 2019-01-10T15:49:49.013

Recursion is not a good approach to this. Performance wise using a recursive cte to increment a counter is the same thing as a cursor. http://www.sqlservercentral.com/articles/T-SQL/74118/

A much better approach is to do this set based. For this task a tally table is ideal. Here is a great article on the topic.

I keep a tally table as a view in my system. It is lightning fast with zero reads.

create View [dbo].[cteTally] as

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )
select N from cteTally
GO

Then for something like this problem it is super simple to use. This will produce the same results with no looping.

declare @endDate datetime = '2019-01-31'
    , @tmpDate datetime = '2018-10-01'

select dateadd(day, t.N - 1, @tmpDate)
from cteTally t
where t.N - 1 <= DATEDIFF(day, @tmpDate, @endDate)

--EDIT--

If you need this to be dynamic you can use a little date math. This will get the data from the beginning of 3 months ago through the end of the current month regardless of when you run this. The date logic might be a little tough to decipher if you haven't seen this kind of thing before. Lynn Pettis has a great article on this topic. http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/

select dateadd(day, t.N - 1, dateadd(month, -3, dateadd(month, datediff(month, 0, getdate()), 0)))
from cteTally t
where t.N - 1 < datediff(day,dateadd(month, -3, dateadd(month, datediff(month, 0, getdate()), 0)), dateadd(month, datediff(month, 0, getdate()) + 1, 0))

The only answer (at the time of commenting) that doesn't use a rCTE, or (even worse) a `WHILE` loop. This is by far the right answer here. — Thom A, Jan 10 '19 at 15:09
Great answer with a good example of tally table. Thank you for sharing this with us. — Eray Balkanli, Jan 10 '19 at 15:11
I don't think his date range should be hard-coded, it was an example he gave, the current month and 3 months before that. It'll waste his time having to go and change that on the first of each month. It's a poor approach to do that. — Cato, Jan 10 '19 at 15:34
@Cato and this was an example as well. It is trivial to adjust this to accommodate the current month and the previous 3. The same concept just need to make the start and end dates dynamic which isn't that tough. I will update this in a second to demonstrate. — Sean Lange, Jan 10 '19 at 15:38
Actually that was the part that wasn't quite working in his code, so it wasn't really trivial at all. You didn't actually give him a re-write that met his requirements. — Cato, Jan 10 '19 at 15:45
@Cato I didn't catch the dynamic requirements from the OP but reading again I think you may be correct. See my edit as it addresses this need and still doesn't need loops or recursion. — Sean Lange, Jan 10 '19 at 15:51

score 4 · Answer 2 · edited Jan 10 '19 at 14:59

4

This will work depending on your version of SQL Server.

with cte as
  (
     select cast(getdate() as date) as   n
     union all
     select  dateadd(DAY,-1,n) from cte where dateadd(DAY,-1,n) > (select eomonth(cast(dateadd(month,-4,getdate()) as date)))
  )
  select * 
  from cte
  order by n desc
  option (maxrecursion 200)

edited Jan 10 '19 at 14:59

Eray Balkanli

7,752
11
48
82

answered Jan 10 '19 at 14:59

Joe

147
1
8

score 2 · Answer 3 · answered Jan 10 '19 at 14:51

2

You're hitting the maxrecursion limit. Increase it as an option:

with cte as
  (
  select getdate() as   n
  union all
  select  dateadd(DAY,-1,n) from cte where month(dateadd(dd,-1,n)) < month(DATEADD(month, -3, getdate())) --and month(DATEADD(month, 0, getdate()))
   union all
  select  dateadd(DAY,-1,n) from cte where month(dateadd(dd,-1,n)) > month(DATEADD(month, 0, getdate()))
  )
select * from cte
OPTION (MAXRECURSION 1000)

answered Jan 10 '19 at 14:51

JohnHC

10,935
1
24
40

2

This still hits the recursion limit...and recursion isn't the best approach here. This screams for a tally or calendar table. – Sean Lange Jan 10 '19 at 14:54

Eray Balkanli · Answer 4 · 2019-01-10T14:57:50.657

2

You can use a temp table for this purpose. With a loop, just add the dates you need to the temp table. Check the query below:

create table #temp (thedate date)
declare @i int = 1
declare @tmpDate datetime = dateadd(month,-2,getdate())

while @tmpDate<=getdate()
begin
  insert into #temp
  values (@tmpDate)

  set @tmpDate = dateadd(day,1,@tmpDate)
end

select * from #temp

EDIT: Based on OP's comment, new query:

create table #temp (thedate date)
declare @i int = 1
declare @endDate datetime = '2019-01-31'
declare @tmpDate datetime = '2018-10-01'

while @tmpDate<=@endDate
begin
  insert into #temp
  values (@tmpDate)

  set @tmpDate = dateadd(day,1,@tmpDate)
end

select * from #temp

edited Jan 10 '19 at 14:57

answered Jan 10 '19 at 14:51

Eray Balkanli

7,752
11
48
82

1

Looping here is not the most efficient approach. – Sean Lange Jan 10 '19 at 14:52
1

@SeanLange You are right, it does not have the best performance but for two months of process, it is pretty fast and useful. – Eray Balkanli Jan 10 '19 at 14:54
1

A tally table would be much simpler to use. :) – Sean Lange Jan 10 '19 at 14:55
1

I get values from 2018-11-10 till 2019-01-10 but the requirement is to get dates from 2018-10-01(beginning of the month) and 2019-01-31(end of the current month). – NewbieSQL Jan 10 '19 at 14:55
1

@NewbieSQL then set your end date as 2019-01-31 instead of using getdate() and set the first value of tmpdate as 2018-10-01. – Eray Balkanli Jan 10 '19 at 14:56
Try this article. http://www.sqlservercentral.com/articles/T-SQL/62867/ – Sean Lange Jan 10 '19 at 14:57

Cato · Accepted Answer · 2019-01-10T15:55:53.360

with cte as
  (
  select dateadd(month,1,dateadd(day, -1* day(getdate()) , cast(getdate() as date) )   ) n
  union all
  select  dateadd(day,-1,n) from cte where month(n)  + year(n) * 12 >= month(getdate())  + year(getdate()) * 12 -3
  ),
 final as (select * from cte except  select top 1 * from cte order by n)
select * from final order by n
OPTION (MAXRECURSION 1000)

or to use dateadd only and avoid the except

with cte as
  (
  select dateadd(day,-1,dateadd(month,1,dateadd(day, 1 - day(getdate()) , cast(getdate() as date)))) n
  union all
  select  dateadd(day,-1,n) from cte where n > dateadd(month,-3,dateadd(day , 1 - day(getdate()),cast(getdate() as date))) 
  )
select * from cte order by n
OPTION (MAXRECURSION 1000)

wnutt · Answer 6 · 2019-01-10T15:55:04.707

If you're using SQL 2012+

SELECT 
    dateadd(dd, number, (dateadd(dd, 1, dateadd(MM, -4, eomonth(getdate()))))) as TheDate
FROM 
    master..spt_values m1
WHERE 
    type = 'P' 
AND dateadd(dd, number, (dateadd(dd, 1, dateadd(MM, -4, eomonth(getdate()))))  ) <= eomonth(getdate())

And for earlier versions of SQL:

SELECT 
    cast(dateadd(dd, number, dateadd(MM, -3, dateadd(dd, -day(getdate())+1, getdate()))) as date)
FROM 
    master..spt_values m1
WHERE 
    type = 'P' 
AND dateadd(dd, number, dateadd(MM, -3, dateadd(dd, -day(getdate())+1, getdate()))) <= dateadd(MM, 1, dateadd(dd, -day(getdate()) , getdate()))

How to get all dates between current month and the two last months

6 Answers6