Print all dates of current year on SQL Server 2008R2

Question

The following codes will print all dates throughout the current year on SQL Server 2008R2

with x (dy, yr)
 as (
select dy, year (dy) yr
from (
     select getdate () - datepart (dy, getdate ()) + 1 dy
     -- the first date of the current year
     ) tmp1
union all
select dateadd (dd, 1, dy), yr
from x
where year (dateadd (dd, 1, dy)) = yr
 )
select x.dy
from x
option (maxrecursion 400)     

But there are some points that I cannot understand

1. As far as I can see, the first date should've been printed 400 times, are all the repetitions filtered out?
2. when I change 400 to less than 364, the following error returns:

[Err] 42000 - [SQL Server]The statement terminated. The maximum recursion 363 has been exhausted before statement completion.

But how does the processor know when the statement is going to complete?

Answer 1

What you are dealing with here is a recursive CTE. You should probably just read more about how it works.

Basically,

• It obtains the first row set from the anchor part (the first SELECT, the left part of UNION ALL).

• That row set becomes aliased as x in the second SELECT (the right part of UNION ALL), called the recursive part.

• The recursive part produces another row set based on x, which becomes a new x at the next iteration. That is, not the combined row set of the initial x and the last result set becomes a new x, but only the last result set.

• The previous step is repeated again against the new x, and the cycle goes on until either of these is true:

  • another iteration produces no result set;

  • the MAXRECURSION limit is reached.

The final result set consists of all the partial result sets obtained from both parts of the recursive CTE.

Applying the above to your particular query:

• The first SELECT produces one row containing this year's 1^st of January (the date), and that becomes the first x table.

• For every row of x the second SELECT produces a row containing the corresponding next date if it belongs to the same year. So, the recursive part's first iteration effectively gives us the 2^nd of January. According to the above description, the result set becomes a new x.

• The following iteration results in the 3^rd of January, the next one produces the 4^th and so on.

• If the MAXRECURSION option value has safely allowed us to arrive at the moment when x contains the 31^st of December, then another iteration will reveal that the next day in fact belongs to a different year. That will result in an empty row set produced, which in turn will result in termination of the recursive CTE's execution.

Answer 2

This is not an answer this is just another way of writing your sql. Andriy M has left you with a cool answer, you should give him the credit for the right answer.

;with x (dy) 
 as ( 
select dateadd(year, datediff(year, 0, getdate()), 0) dy 
union all 
select dy + 1 
from x 
where year (dy) = year(dy+1) 
) 
select x.dy 
from x 
option (maxrecursion 400)