Recipe9.8.Listing Quarter Start and End Dates for the Year

Recipe 9.8. Listing Quarter Start and End Dates for the Year

Problem

You want to return the start and end dates for each of the four quarters of a given year.

Solution

There are four quarters to a year, so you know you will need to generate four rows. After generating the desired number of rows, simply use the date functions supplied by your RDBMS to return the quarter the start and end dates fall into. Your goal is to produce the following result set (one again, the choice to use the current year is arbitrary):

 QTR Q_START     Q_END --- ----------- -----------   1 01-JAN-2005 31-MAR-2005   2 01-APR-2005 30-JUN-2005   3 01-JUL-2005 30-SEP-2005   4 01-OCT-2005 31-DEC-2005 

DB2

Use table EMP and the window function ROW_NUMBER OVER to generate four rows. Alternatively, you can use the WITH clause to generate rows (as many of the recipes do), or you can query against any table with at least four rows. The following solution uses the ROW_NUMBER OVER approach:

  1 select quarter(dy-1 day) QTR,  2        dy-3 month Q_start,  3        dy-1 day Q_end  4   from (  5 select (current_date -  6          (dayofyear(current_date)-1) day  7            + (rn*3) month) dy  8   from (  9 select row_number( )over( ) rn 10   from emp 11  fetch first 4 rows only 12         ) x 13         ) y 

Oracle

Use the function ADD_MONTHS to find the start and end dates for each quarter. Use ROWNUM to represent the quarter the start and end dates belong to. The following solution uses table EMP to generate four rows.

 1 select rownum qtr, 2        add_months(trunc(sysdate,'y'),(rownum-1)*3) q_start, 3        add_months(trunc(sysdate,'y'),rownum*3)-1 q_end 4   from emp 5  where rownum <= 4 

PostgreSQL

Use the function GENERATE_SERIES to generate the required four quarters. Use the DATE_TRUNC function to truncate the dates generated for each quarter down to year and month. Use the TO_CHAR function to determine which quarter the start and end dates belong to:

  1 select to_char(dy,'Q') as QTR,  2        date(  3          date_trunc('month',dy)-(2*interval '1 month')  4        ) as Q_start,  5        dy as Q_end  6   from (  7 select date(dy+((rn*3) * interval '1 month'))-1 as dy  8   from (  9 select rn, date(date_trunc('year',current_date)) as dy 10   from generate_series(1,4) gs(rn) 11        ) x 12        ) y 

MySQL

Use table T500 to generate four rows (one for each quarter). Use functions DATE_ ADD and ADDDATE to create the start and end dates for each quarter. Use the QUARTER function to determine which quarter the start and end dates belong to:

  1 select quarter(adddate(dy,-1)) QTR,  2        date_add(dy,interval -3 month) Q_start,  3        adddate(dy,-1) Q_end  4   from (  5 select date_add(dy,interval (3*id) month) dy  6   from (  7 select id,  8        adddate(current_date,-dayofyear(current_date)+1) dy  9  from t500 10 where id <= 4 11        ) x 12        ) y 

SQL Server

Use the recursive WITH clause to generate four rows. Use the function DATEADD to find the start and end dates. Use the function DATEPART to determine which quarter the start and end dates belong to:

  1  with x (dy,cnt)  2    as (  3 select dateadd(d,-(datepart(dy,getdate( ))-1),getdate( )),  4        1  5   from t1  6  union all  7 select dateadd(m,3,dy), cnt+1  8   from x  9  where cnt+1 <= 4 10 ) 11 select datepart(q,dateadd(d,-1,dy)) QTR, 1         dateadd(m,-3,dy) Q_start, 13        dateadd(d,-1,dy) Q_end 14   from x 15 order by 1 

Discussion

DB2

The first step is to generate four rows (with values 1 through 4) for each quarter in the year. Inline view X uses the window function ROW_NUMBER OVER and the FETCH FIRST clause to return only four rows from EMP. The results are shown below:

  select row_number()over() rn   from emp  fetch first 4 rows only RN --  1  2  3  4 

The next step is to find the first day of the year, then add n months to it, where n is three times RN (you are adding 3, 6, 9, and 12 months to the first day of the year). The results are shown below:

  select (current_date         (dayofyear(current_date)-1) day           + (rn*3) month) dy    from ( select row_number()over() rn   from emp  fetch first 4 rows only        ) x DY ----------- 01-APR-2005 01-JUL-2005 01-OCT-2005 01-JAN-2005 

At this point, the values for DY are one day after the end date for each quarter. The next step is to get the start and end dates for each quarter. Subtract one day from DY to get the end of each quarter, and subtract three months from DY to get the start of each quarter. Use the QUARTER function on DY-1 (the end date for each quarter) to determine which quarter the start and end dates belong to.

Oracle

The combination of ROWNUM, TRUNC, and ADD_MONTHS makes this solution very easy. To find the start of each quarter simply add n months to the first day of the year, where n is (ROWNUM-1)*3 (giving you 0,3,6,9). To find the end of each quarter add n months to the first day of the year, where n is ROWNUM*3, and subtract one day. As an aside, when working with quarters, you may also find it useful to use TO_CHAR and/or TRUNC with the 'q' formatting option.

PostgreSQL

The first step is to truncate the current date to the first day of the year using the DATE_TRUNC function. Next, add n months, where n is RN (the values returned by GENERATE_SERIES) times three, and subtract one day. The results are shown below:

  select date(dy+((rn*3) * interval '1 month'))-1 as dy   from ( select rn, date(date_trunc('year',current_date)) as dy   from generate_series(1,4) gs(rn)        ) x DY ----------- 31-MAR-2005 30-JUN-2005 30-SEP-2005 31-DEC-2005 

Now that you have the end dates for each quarter, the final step is to find the start date by subtracting two months from DY then truncating to the first day of the month by using the DATE_TRUNC function. Use the TO_CHAR function on the end date for each quarter (DY) to determine which quarter the start and end dates belong to.

MySQL

The first step is to find the first day of the year by using functions ADDDATE and DAYOFYEAR, then adding n months to the first day of the year, where n is T500.ID times three, by using the DATE_ADD function. The results are shown below:

  select date_add(dy,interval (3*id) month) dy   from ( select id,        adddate(current_date,-dayofyear(current_date)+1) dy   from t500  where id <= 4        ) x   DY ----------- 01-APR-2005 01-JUL-2005 01-OCT-2005 01-JAN-2005 

At this point the dates are one day after the end of each quarter; to find the end of each quarter, simply subtract one day from DY. The next step is to find the start of each quarter by subtracting three months from DY. Use the QUARTER function on the end date of each quarter to determine which quarter the start and end dates belong to.

SQL Server

The first step is to find the first day of the year, then recursively add n months, where n is three times the current iteration (there are four iterations, therefore, you are adding 3*1 months, 3*2 months, etc.), using the DATEADD function. The results are shown below:

  with x (dy,cnt)    as ( select dateadd(d,-(datepart(dy,getdate())-1),getdate()),        1   from t1  union all select dateadd(m,3,dy), cnt+1   from x  where cnt+1 <= 4 ) select dy   from x DY ----------- 01-APR-2005 01-JUL-2005 01-OCT-2005 01-JAN-2005 

The values for DY are one day after the end of each quarter. To get the end of each quarter, simply subtract one day from DY by using the DATEADD function. To find the start of each quarter, use the DATEADD function to subtract three months from DY. Use the DATEPART function on the end date for each quarter to determine which quarter the start and end dates belong to.