SQL 计算一个季度的开始日期和结束日期

SQL 计算一个季度的开始日期和结束日期，以 yyyyq 格式（前面 4 位是年份，最后 1 位是季度序号）给出了年份和季度序号，你希望找出该季度的开始日期和结束日期。

SQL 计算一个季度的开始日期和结束日期问题描述

以 yyyyq 格式（前面 4 位是年份，最后 1 位是季度序号）给出了年份和季度序号，你希望找出该季度的开始日期和结束日期。

SQL 计算一个季度的开始日期和结束日期解决方案

本解决方案的关键之处在于如何使用模函数从 yyyyq 值里提取出季度序号。（如果不想使用模计算，也可以简单地借助子字符串函数提取出 yyyyq 的最后一个数字以得到季度序号，因为我们知道前面 4 位表示年份。）得到了季度序号之后，只要乘以 3，就能计算出该季度最后一个月的月份。下述解决方案用到的内嵌视图 X 里包含了由年份和季度序号组合而成的 4 行数据。查询内嵌视图 X 的话，会得到下面的结果集。

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
使用 SUBSTR 函数从内嵌视图 X 里提取出年份，使用 MOD 函数提取出对应的季度序号。

 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
使用 SUBSTR 函数从内嵌视图 X 里提取出年份，使用 MOD 函数提取出对应的季度序号。

 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
使用 SUBSTR 函数从内嵌视图 X 里提取出年份，使用 MOD 函数提取出对应的季度序号。

 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
使用 SUBSTR 函数从内嵌视图 X 里提取出年份，使用 MOD 函数提取出对应的季度序号。

 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
使 SUBSTRING 函数从内嵌视图 X 里提取出年份，使用取模运算符（%）提取出对应的季度序号。

 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 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

SQL 计算一个季度的开始日期和结束日期扩展知识

DB2
首先找出需要处理的年份和季度序号。调用 SUBSTR 函数从内嵌视图 X（X.YRQ）里提取出年份。为了获取季度序号，用 YRQ 对 10 取模。得到季度序号后，乘以 3 即得到该季度最后一个月的月份，结果如下所示。

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

现在已经得到了年份和每个季度最后一个月的月份。利用这些值可以构建出每个季度最后一个月第一天的日期。使用连接运算符||把年份和月份连接起来，然后使用 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

上述 Q_END 值是每个季度最后一个月的第一天。为了计算出该季度的结束日期，只要加上 1 个月，然后再减去 1 天即可。为了找出每个季度的开始日期，要从 Q_END 里减去 2 个月。
Oracle
首先找出需要处理的年份和季度序号。调用 SUBSTR 函数从内嵌视图 X（X.YRQ）里提取出年份。为获取季度序号，用 YRQ 对 10 取模。得到季度序号后，乘以 3 即得到该季度最后一个月的月份，结果如下所示。

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

现在已经得到了年份和每个季度最后一个月的月份。利用这些值可以构建出每个季度最后一个月第一天的日期。使用连接运算符||把年份和月份连接起来，然后使用 TO_DATE 函数将其转换为日期类型。

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

上述 Q_END 值是每个季度最后一个月的第一天。为计算出该季度的结束日期，针对 Q_END 调用 LAST_DAY 函数即可。为找出每个季度的开始日期，要调用 ADD_MONTHS 函数从 Q_END 里减去 2 个月。
PostgreSQL
首先找出需要处理的年份和季度序号。调用 SUBSTR 函数从内嵌视图 X（X.YRQ）里提取出年份。为获取季度序号，用 YRQ 对 10 取模。得到季度序号后，乘以 3 即得到该季度最后一个月的月份，结果如下所示。

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

上述 Q_END 值是每个季度最后一个月的第一天。为计算出该季度的结束日期，在 Q_END 基础上加上 1 个月，再减去 1 天即可。为找出每个季度的开始日期，要从 Q_END 里减去 2 个月。最后要把计算结果转换为日期类型。
MySQL
首先找出需要处理的年份和季度序号。调用 SUBSTR 函数从内嵌视图 X（X.YRQ）里提取出年份。为获取季度序号，用 YRQ 对 10 取模。得到季度序号后，乘以 3 即得到该季度最后一个月的月份，结果如下所示。

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

现在已经得到了年份和每个季度最后一个月的月份。利用这些值可以构建出每个季度的结束日期。先使用 CONCAT 函数把年份和月份连接起来，然后使用 STR_TO_DATE 函数将其转换为日期类型。最后，调用 LAST_DAY 函数计算出每个季度的最后一天。

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

因为我们已经知道了每个季度的结束日期，剩下的工作就是要计算出开始日期。调用 DAY 函数计算出每个季度的结束日期分别是当前月份的第几天，接着调用 ADDDATE 函数从 Q_END 里减去该计算结果，这样就得到了前一个月的最后一天；再加上 1 天，就得到了每个季度最后一个月的第一天。最后，调用 DATE_ADD 函数从上述结果日期里减去 2 个月，至此我们就得到了每个季度的开始日期。
SQL Server
首先找出需要处理的年份和季度序号。调用 SUBSTRING 函数从内嵌视图 X（X.YRQ）里提取出年份。为获取季度序号，用 YRQ 对 10 取模。得到季度序号后，乘以 3 即得到该季度最后一个月的月份，结果如下所示。

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

现在已经得到了年份和每个季度最后一个月的月份。利用这些值可以构建出每个季度最后一个月第一天的日期。使用连接运算符 + 把年份和月份连接起来，然后使用 CAST 函数将其转换为日期类型。

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