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
现在已经得到了年份和每个季度最后一个月的月份。利用这些值可以构建出每个季度最后一个月第一天的日期。使用连接运算符||
把年份和月份连接起来,然后使用 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
基础上加上 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
上述 Q_END
值是每个季度最后一个月的第一天。为计算出该季度的结束日期,只要调用 DATEADD
函数加上 1 个月,然后再减去 1 天即可。为找出每个季度的开始日期,需要调用 DATEADD
函数从 Q_END
里减去 2 个月。