date query

Why does this query return 13 records? I want it only to return 12 ... the one between the dates

DECLARE @END DATE = '12/31/2017';
DECLARE @Start DATE = DATEADD(MONTH, -11, @END);


WITH _DateList ([DATE])
AS (SELECT  @Start [DATE]
    UNION ALL
    SELECT DATEADD(MONTH, 1, [DATE])
    FROM _DateList
    WHERE [DATE] <=  @END
   )
SELECT  * FROM _DateList

Open in new window

vbnetcoderAsked:
Who is Participating?
 
NorieVBA ExpertCommented:
Forgot to double check the results, try this.

DECLARE @END DATE =  '12/1/2017';
DECLARE @Start DATE = DATEADD(MONTH, -11, @END);

WITH _DateList ([DATE])
AS (SELECT  @Start [DATE]
    UNION ALL
    SELECT DATEADD(MONTH, 1, [DATE])
    FROM _DateList
    WHERE [DATE] <@END
   )
SELECT  * FROM _DateList
0
 
NorieVBA ExpertCommented:
Which dates are you trying to generate?
0
 
vbnetcoderAuthor Commented:
In the case of my example it would be

01/17
02/17
03/17
04/17
05/17
06/17
07/17
08/17
09/17
10/17  
11/17
12/17
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
NorieVBA ExpertCommented:
What about the day of month?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
the way recursive cte works is it will execute the query first, get a result then it validate the where clause before it passes the last result set back to the same query.
In your case  your 12th entry will be '2017-12-28' which is less than  '2017-12-31', so it will pass '2017-12-28' back to the query where it adds another month and returns the result '2018-01-28' , now before passing this result back to the query, it checks the condition and as  Jan 28 is not less than dec 31, it will stop recursion, but you eventually will have this value within the cte.
0
 
vbnetcoderAuthor Commented:
Aneesh Retnakaran

Is there anything I can do to get the result I want?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
are you trying to get the last day of the month ? if so use this

DECLARE @END DATE = '12/31/2017';
DECLARE @Start DATE = DATEADD(MONTH, -11, @END);

WITH _DateList ([DATE])
AS (SELECT  @Start [DATE]
    UNION ALL
    SELECT CAST( DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,[date])+2,0))AS DATE )  --DATEADD(MONTH, 1, [DATE])
    FROM _DateList
    WHERE [DATE] <  '12/31/2017'
   )
SELECT  * FROM _DateList
GO
0
 
NorieVBA ExpertCommented:
If it is the last day of each month in 2017 try this,

DECLARE @END DATE = '12/31/2017';
DECLARE @Start DATE = DATEADD(MONTH, -11, @END);

WITH _DateList ([DATE])
AS (SELECT  @Start [DATE]
    UNION ALL
    SELECT EOMONTH(DATEADD(MONTH, 1, [DATE]))
    FROM _DateList
    WHERE [DATE] <  @END
   )
SELECT  * FROM _DateList
0
 
vbnetcoderAuthor Commented:
I want the first of the month
0
 
NorieVBA ExpertCommented:
For the first day of each month in 2017 try this.

DECLARE @END DATE =  '1/1/2018';
DECLARE @Start DATE = DATEADD(MONTH, -12, @END);

WITH _DateList ([DATE])
AS (SELECT  @Start [DATE]
    UNION ALL
    SELECT DATEADD(MONTH, 1, [DATE])
    FROM _DateList
    WHERE [DATE] <=@END
   )
SELECT  * FROM _DateList
0
 
vbnetcoderAuthor Commented:
Norie,

That returns 14 records.  I want only 12 one for each month
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
DECLARE @END DATE = '12/31/2017';
DECLARE @Start DATE = DATEADD(MONTH, -11, @END);
SET @End = DATEADD(MONTH, -1 , DATEADD(d, 1, EOMONTH(@end) ));

WITH _DateList ([DATE])
AS (SELECT  @Start [DATE]
    UNION ALL
    SELECT CAST( DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,[date])+2,0))AS DATE )  --DATEADD(MONTH, 1, [DATE])
    FROM _DateList
    WHERE [DATE] <  '12/31/2017'
   )
SELECT  * FROM _DateList
GO

DECLARE @END DATE = '12/31/2017';
DECLARE @Start DATE = DATEADD(D,1, EOMONTH( DATEADD(MONTH, -11, @END) , -1)) ;
SET @End = DATEADD(MONTH, -1 , DATEADD(d, 1, EOMONTH(@end) ));

WITH _DateList ([DATE])
AS (SELECT  @Start [DATE]
    UNION ALL
    SELECT   DATEADD(D,1, EOMONTH([date]))
    FROM _DateList
    WHERE [DATE] <  @END
   )
SELECT  * FROM _DateList
0
 
vbnetcoderAuthor Commented:
ty
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.