Returns date range by quarter?

Question

I am looking for a query that can returns a series of date range that is one quarter long.

For example, if the input is 2/1/2013 and 3/31/2014, then the output would look like:

start     end
2/1/2013  4/30/2013
5/1/2013  7/31/2013
8/1/2013  10/31/2013
11/1/2013 1/31/2014
2/1/2014  3/31/2014

Notice that the last quarter is only 2 months long. Thanks for help in advance.

Please specify the RDBMS that you are targeting by adding the appropriate tag (Oracle, SQL Server, MySQL, etc.). There may be answers that take advantage of language or product features that are not universally supported. Also, by tagging it with a specific RDBMS, your question may receive attention from people better suited to answer it — Taryn, Mar 20 '13 at 14:05
Usually for this type of thing you want to start with a [calendar table](http://stackoverflow.com/questions/1201874/calendar-table-for-data-warehouse) (example is for SQL Server, but should be translatable), which would allow you to specify 'quarter'. The rest then becomes a fairly trivial `MIN()`/`MAX()` query. — Clockwork-Muse, Mar 20 '13 at 16:11

score 0 · Answer 1 · answered Mar 21 '13 at 14:34

Just want to add that this is what I did after I did a bit of googling. I was thinking of some more efficient way but I think this is sufficient for my purpose. The first part is to populate a date table, the second part to calculate the quarter range.

DECLARE @StartDate SMALLDATETIME
DECLARE @EndDate SMALLDATETIME

SET @StartDate = '1/1/2011'
SET @EndDate = '12/31/2011'

-- creates a date table, not needed if there is one already
DECLARE @date TABLE ( [date] SMALLDATETIME )

DECLARE @offset INT
SET @offset = 0

WHILE ( @offset < DATEDIFF(dd, @StartDate, DATEADD(dd, 1, @EndDate)) ) 
    BEGIN
        INSERT  INTO @date ( [date] )
    VALUES  ( DATEADD(dd, @offset, @StartDate) )
    SELECT  @offset = @offset + 1
END ;

WITH    dateCTE
      AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY [date] ASC ) AS qID ,
                    [date] AS qStart ,
                    CASE WHEN DATEADD(dd, -1, DATEADD(q, 1, [date])) > @EndDate
                         THEN @EndDate
                         ELSE DATEADD(dd, -1, DATEADD(q, 1, [date]))
                    END AS qEnd
           FROM     @date
           WHERE    [date] = @StartDate
                    OR ( DATEDIFF(mm, @StartDate, [date]) % 3 = 0
                         AND DATEPART(dd, [date]) = DATEPART(dd,
                                                          @StartDate)
                       )
         )

Returns date range by quarter?

1 Answers1