Get dates between start and end date

Question

Hi I have a question.

I need to be able to find the dates in months based on the start and end date using a SQL Query. ie. StartDate: 2013-08-01 00:00:00.000, EndDate: 2015-08-01 00:00:00.000 The results has to be as follow:

2013-08-01 00:00:00.000,
2013-09-01 00:00:00.000,
2013-10-01 00:00:00.000,
2013-11-01 00:00:00.000,
2013-12-01 00:00:00.000,
2014-01-01 00:00:00.000,
2014-02-01 00:00:00.000,
2014-03-01 00:00:00.000,
2014-04-01 00:00:00.000,
2014-05-01 00:00:00.000,
2014-06-01 00:00:00.000,
2014-07-01 00:00:00.000,
2014-08-01 00:00:00.000,
2014-09-01 00:00:00.000,
2014-10-01 00:00:00.000,
2014-11-01 00:00:00.000,
2014-12-01 00:00:00.000,
2015-01-01 00:00:00.000,
2015-02-01 00:00:00.000,
2015-03-01 00:00:00.000,
2015-04-01 00:00:00.000,
2015-05-01 00:00:00.000,
2015-06-01 00:00:00.000,
2015-07-01 00:00:00.000,
2015-08-01 00:00:00.000

Can you please help. Thanks in advance

Answer 1

If it were me, I would make a Calendar table. Sure you could do lots of fancy SQL with datediff and what not but it always just seems very handy to have a table with 100 years of dates in it to join to. If you are going to be doing a lot of stuff with dates in your data, it's a nice thing to have. It does feel a little 'cheap' to do it this way, but it's worked well for me.

How to create a Calendar table for 100 years in Sql

Then you can simply join to this, and grab the dates in that range.

update

As, @scsimon pointed out, the accepted answer in the link might not be ideal. Give the link a read though, some good ideas in there to make the table.

Answer 2

Tally table would be really fast...

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 '20130801'
union all
select dateadd(month,N,'20130801') 
from cteTally
where n <= datediff(month,'20130801','20150801')

More clearly using parameters

declare @startDate date = '20130801'
declare @endDate date = '20150801'

;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 @startDate
union all
select dateadd(month,N,@startDate) 
from cteTally
where n <= datediff(month,@startDate,@endDate)