Recipe9.9.Determining Quarter Start and End Dates for a Given Quarter

Recipe 9.9. Determining Quarter Start and End Dates for a Given Quarter

Problem

When given a year and quarter in the format of YYYYQ (four-digit year, one-digit quarter), you want to return the quarter's start and end dates.

Solution

The key to this solution is to find the quarter by using the modulus function on the YYYYQ value. (As an alternative to modulo, since the year format is four digits, you can simply substring out the last digit to get the quarter.) Once you have the quarter, simply multiply by 3 to get the ending month for the quarter. In the solutions that follow, inline view X will return all four year and quarter combinations. The result set for inline view X is as follows:

  select 20051 as yrq from t1 union all select 20052 as yrq from t1 union all select 20053 as yrq from t1 union all select 20054 as yrq from t1     YRQ -------   20051   20052   20053   20054

DB2

Use the function SUBSTR to return the year from inline view X. Use the MOD function to determine which quarter you are looking for:

  1 select (q_end-2 month) q_start,  2        (q_end+1 month)-1 day q_end  3   from (  4 select date(substr(cast(yrq as char(4)),1,4) ||'-'||  5        rtrim(cast(mod(yrq,10)*3 as char(2))) ||'-1') q_end  6   from (  7 select 20051 yrq from t1 union all  8 select 20052 yrq from t1 union all  9 select 20053 yrq from t1 union all 10 select 20054 yrq from t1 11        ) x 12        ) y

Oracle

Use the function SUBSTR to return the year from inline view X. Use the MOD function to determine which quarter you are looking for:

  1 select add_months(q_end,-2) q_start,  2        last_day(q_end) q_end  3   from (  4 select to_date(substr(yrq,1,4)||mod(yrq,10)*3,'yyyymm') q_end  5   from (  6 select 20051 yrq from dual union all  7 select 20052 yrq from dual union all  8 select 20053 yrq from dual union all  9 select 20054 yrq from dual 10        ) x 11        ) y

PostgreSQL

Use the function SUBSTR to return the year from the inline view X. Use the MOD function to determine which quarter you are looking for:

  1 select date(q_end-(2*interval '1 month')) as q_start,  2        date(q_end+interval '1 month'-interval '1 day') as q_end  3   from (  4 select to_date(substr(yrq,1,4)||mod(yrq,10)*3,'yyyymm') as q_end  5   from (  6 select 20051 as yrq from t1 union all  7 select 20052 as yrq from t1 union all  8 select 20053 as yrq from t1 union all  9 select 20054 as yrq from t1 10        ) x 11        ) y

MySQL

Use the function SUBSTR to return the year from the inline view X. Use the MOD function to determine which quarter you are looking for:

  1 select date_add(  2         adddate(q_end,-day(q_end)+1),  3                interval -2 month) q_start,  4        q_end  5   from (  6 select last_day(  7     str_to_date(  8          concat(  9          substr(yrq,1,4),mod(yrq,10)*3),'%Y%m')) q_end 10   from ( 11 select 20051 as yrq from t1 union all 12 select 20052 as yrq from t1 union all 13 select 20053 as yrq from t1 union all 14 select 20054 as yrq from t1 15        ) x 16        ) y

SQL Server

Use the function SUBSTRING to return the year from the inline view X. Use the modulus function (%) to determine which quarter you are looking for:

  1 select dateadd(m,-2,q_end) q_start,  2        dateadd(d,-1,dateadd(m,1,q_end)) q_end  3   from (  4 select cast(substring(cast(yrq as varchar),1,4)+'-'+  5        cast(yrq%10*3 as varchar)+'-1' as datetime) q_end  6   from (  7 select 20051 as yrq from t1 union all     8 select 20052 as yrq from t1 union all     9 select 20052 as yrq from t1 union all      10 select 20054 as yrq from t1 11        ) x 12        ) y

Discussion

DB2

The first step is to find the year and quarter you are working with. Substring out the year from inline view X (X.YRQ) using the SUBSTR function. To get the quarter, use modulus 10 on YRQ. Once you have the quarter, multiply by 3 to get the end month for the quarter. The results are shown below:

  select substr(cast(yrq as char(4)),1,4) yr,        mod(yrq,10)*3 mth   from ( select 20051 yrq from t1 union all select 20052 yrq from t1 union all select 20053 yrq from t1 union all select 20054 yrq from t1        ) x YR      MTH ---- ------ 2005      3 2005      6 2005      9 2005     12

At this point you have the year and end month for each quarter. Use those values to construct a date, specifically, the first day of the last month for each quarter. Use the concatenation operator "||" to glue together the year and month, then use the DATE function to convert to a date:

  select date(substr(cast(yrq as char(4)),1,4) ||'-'||        rtrim(cast(mod(yrq,10)*3 as char(2))) ||'-1') q_end   from ( select 20051 yrq from t1 union all select 20052 yrq from t1 union all select 20053 yrq from t1 union all select 20054 yrq from t1        ) x Q_END ----------- 01-MAR-2005 01-JUN-2005 01-SEP-2005 01-DEC-2005

The values for Q_END are the first day of the last month of each quarter. To get to the last day of the month add one month to Q_END, then subtract one day. To find the start date for each quarter subtract two months from Q_END.

Oracle

  select substr(yrq,1,4) yr, mod(yrq,10)*3 mth   from ( select 20051 yrq from dual union all select 20052 yrq from dual union all select 20053 yrq from dual union all select 20054 yrq from dual        ) x YR      MTH ---- ------ 2005      3 2005      6 2005      9 2005     12

  select to_date(substr(yrq,1,4)||mod(yrq,10)*3,'yyyymm') q_end   from ( select 20051 yrq from dual union all select 20052 yrq from dual union all select 20053 yrq from dual union all select 20054 yrq from dual        ) x Q_END ----------- 01-MAR-2005 01-JUN-2005 01-SEP-2005 01-DEC-2005

The values for Q_END are the first day of the last month of each quarter. To get to the last day of the month use the LAST_DAY function on Q_END. To find the start date for each quarter subtract two months from Q_END using the ADD_MONTHS function.

PostgreSQL

  select substr(yrq,1,4) yr, mod(yrq,10)*3 mth   from ( select 20051 yrq from dual union all select 20052 yrq from dual union all select 20053 yrq from dual union all select 20054 yrq from dual        ) x YR        MTH ----  ------- 2005        3 2005        6 2005        9 2005       12

  select to_date(substr(yrq,1,4)||mod(yrq,10)*3,'yyyymm') q_end   from ( select 20051 yrq from dual union all select 20052 yrq from dual union all select 20053 yrq from dual union all select 20054 yrq from dual        ) x Q_END ----------- 01-MAR-2005 01-JUN-2005 01-SEP-2005 01-DEC-2005

The values for Q_END are the first day of the last month of each quarter. To get to the last day of the month add one month to Q_END and subtract one day. To find the start date for each quarter subtract two months from Q_END. Cast the final result as dates.

MySQL

  select substr(yrq,1,4) yr, mod(yrq,10)*3 mth   from ( select 20051 yrq from dual union all select 20052 yrq from dual union all select 20053 yrq from dual union all select 20054 yrq from dual        ) x YR      MTH ---- ------ 2005      3 2005      6 2005      9 2005     12

At this point you have the year and end month for each quarter. Use those values to construct a date, specifically, the last day of each quarter. Use the CONCAT function to glue together the year and month, then use the STR_TO_DATE function to convert to a date. Use the LAST_DAY function to find the last day for each quarter:

  select last_day(     str_to_date(          concat(          substr(yrq,1,4),mod(yrq,10)*3),'%Y%m')) q_end   from ( select 20051 as yrq from t1 union all select 20052 as yrq from t1 union all select 20053 as yrq from t1 union all select 20054 as yrq from t1        ) x Q_END ----------- 31-MAR-2005 30-JUN-2005 30-SEP-2005 31-DEC-2005

Because you already have the end of each quarter, all that's left is to find the start date for each quarter. Use the DAY function to return the day of the month the end of each quarter falls on, and subtract that from Q_END using the ADDDATE function to give you the end of the prior month; add one day to bring you to the first day of the last month of each quarter. The last step is to use the DATE_ADD function to subtract two months from the first day of the last month of each quarter to get you to the start date for each quarter.

SQL Server

The first step is to find the year and quarter you are working with. Substring out the year from inline view X (X.YRQ) using the SUBSTRING function. To get the quarter, use modulus 10 on YRQ. Once you have the quarter, multiply by 3 to get the end month for the quarter. The results are shown below:

  select substring(yrq,1,4) yr, yrq%10*3 mth   from ( select 20051 yrq from dual union all select 20052 yrq from dual union all select 20053 yrq from dual union all select 20054 yrq from dual        ) x YR        MTH ----   ------ 2005        3 2005        6 2005        9 2005       12

At this point, you have the year and end month for each quarter. Use those values to construct a date, specifically, the first day of the last month for each quarter. Use the concatenation operator "+" to glue together the year and month, then use the CAST function to convert to a date:

  select cast(substring(cast(yrq as varchar),1,4)+'-'+        cast(yrq%10*3 as varchar)+'-1' as datetime) q_end   from ( select 20051 yrq from t1 union all select 20052 yrq from t1 union all select 20053 yrq from t1 union all select 20054 yrq from t1        ) x Q_END ----------- 01-MAR-2005 01-JUN-2005 01-SEP-2005 01-DEC-2005

The values for Q_END are the first day of the last month of each quarter. To get to the last day of the month add one month to Q_END and subtract one day using the DATEADD function. To find the start date for each quarter subtract two months from Q_END using the DATEADD function.