CODEWITHSUNDEEP
sqlsql-servert-sql
NewbieSQL
NewbieSQL

Reputation: 43

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

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.

Upvotes: 2

Views: 1812

Answers (6)

Cato
Cato

Reputation: 3701

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)

Upvotes: 2

wnutt
wnutt

Reputation: 549

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()))

Upvotes: 2

Sean Lange
Sean Lange

Reputation: 33571

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))

Upvotes: 5

Joe
Joe

Reputation: 146

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)

Upvotes: 4

Eray Balkanli
Eray Balkanli

Reputation: 7960

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

Upvotes: 2

JohnHC
JohnHC

Reputation: 11195

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)

Upvotes: 2

Related Questions