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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
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
NorieAnalyst Assistant Commented:
What about the day of month?
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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
NorieAnalyst Assistant Commented:
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
NorieAnalyst Assistant Commented:
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
NorieAnalyst Assistant Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.