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

The above mentioned dates relates to one record. Each row in the table has its on start and end date. — John Doe, Aug 21 '18 at 14:59
@scsimon: I think the question that is being asked here by OP : is there a rowsource for DATE values... is there a way to generate a set of date values in a specified range, like a view that we can select from. — spencer7593, Aug 21 '18 at 14:59
@spencer7593 sorry forgot to delete the comment after i removed the duplicate vote. — S3S, Aug 21 '18 at 15:00
I would use a tally table here. http://www.sqlservercentral.com/articles/T-SQL/62867/ As for specific implementation I am at a loss because it isn't really clear what you are trying to do. — Sean Lange, Aug 21 '18 at 15:00

sniperd · Answer 1 · 2018-08-21T14:57:36.647

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.

I wouldn't use the accepted answer here... it will be slow. Just a heads up — S3S, Aug 21 '18 at 14:57

score 2 · Answer 2 · answered Aug 21 '18 at 15:00

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)

Code Different · Answer 3 · 2018-08-21T15:00:17.180

1

There are many approaches to solving this. The one I frequently use is a recursive CTE:

DECLARE @StartDate      date = '2013-08-01'
DECLARE @EndDate        date = '2015-08-01'

;WITH
    cte AS
    (
        SELECT      @StartDate      AS CalendarDate
        UNION ALL
        SELECT      DATEADD(MONTH, 1, CalendarDate)
        FROM        cte
        WHERE       CalendarDate < @EndDate
    )

SELECT      CalendarDate
FROM        cte
OPTION      (MAXRECURSION 0)

For a small range of values (12 months x 100 years is small), this is pretty fast. It becomes slow when you need to get million of rows. Depends on your problem at hand, you may need a different solution.

edited Aug 21 '18 at 15:00

answered Aug 21 '18 at 14:57

Code Different

90,614
16
144
163

1

Just FYI. Using a recursive cte like this can lead to horrible performance problems. http://www.sqlservercentral.com/articles/T-SQL/74118/ The example here is a small set so it won't matter much. But I would prefer a tally table instead, super crazy fast. – Sean Lange Aug 21 '18 at 14:59
I'm very well aware of the performance issue with recursive CTE. Depending on the problem it may or may not matter. A tally table is ideal, but sometimes you don't have permission to set one up in the database. – Code Different Aug 21 '18 at 15:02
1

That would be shameful for the DBA who makes that rule. But fortunately you can create one inline using ctes like scsimon did above. That type of tally table is stupidly fast as it generates zero reads to create thousands of rows. – Sean Lange Aug 21 '18 at 15:07
1

Full disclosure, @SeanLange taught me tally tables – S3S Aug 22 '18 at 00:44
Thank you so much this really help me, much appreciated – John Doe Aug 22 '18 at 06:52
Full disclosure - Jeff Moden shined the light on tally tables to me. I simply pass on the torch. And I believe that Itzik Ben-Gan is commonly credited with the idea of the cte tally table which is so crazy fast. Prior to that most people used a persistent table. – Sean Lange Aug 22 '18 at 13:48

Get dates between start and end date

3 Answers3