选读SQL经典实例笔记07_日期处理（下）.png
1. 一个季度的开始日期和结束日期
1.1. 以yyyyq格式（前面4位是年份，最后1位是季度序号）给出了年份和季度序号
1.2. DB2
1.2.1. sql
select (q_end-2 month) q_start,
(q_end+1 month)-1 day q_end
from (
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
) y
1.3. Oracle
1.3.1. sql
select add_months(q_end,-2) q_start,
last_day(q_end) q_end
from (
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
) y
1.4. PostgreSQL
1.4.1. sql
select date(q_end-(2*interval '1 month')) as q_start,
date(q_end+interval '1 month'-interval '1 day') as q_end
from (
select to_date(substr(yrq,1,4)||mod(yrq,10)*3,'yyyymm') as 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
) y
1.5. MySQL
1.5.1. sql
select date_add(
adddate(q_end,-day(q_end)+1),
interval -2 month) q_start,
q_end
from (
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
) y
1.6. SQL Server
1.6.1. sql
select dateadd(m,-2,q_end) q_start,
dateadd(d,-1,dateadd(m,1,q_end)) q_end
from (
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 PRE_MID_DATA.dbo.para_country union all
select 20052 yrq from PRE_MID_DATA.dbo.para_country union all
select 20053 yrq from PRE_MID_DATA.dbo.para_country union all
select 20054 yrq from PRE_MID_DATA.dbo.para_country
) x
) y
2. 每个季度的开始日期和结束日期
2.1. DB2
2.1.1. sql
select quarter(dy-1 day) QTR,
dy-3 month Q_start,
dy-1 day Q_end
from (
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
) y
2.2. Oracle
2.2.1. sql
select rownum qtr,
add_months(trunc(sysdate,'y'),(rownum-1)*3) q_start,
add_months(trunc(sysdate,'y'),rownum*3)-1 q_end
from emp
where rownum ＜= 4
2.3. PostgreSQL
2.3.1. sql
select to_char(dy,'Q') as QTR,
date(
date_trunc('month',dy)-(2*interval '1 month')
) as Q_start,
dy as Q_end
from (
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
) y
2.4. MySQL
2.4.1. sql
select quarter(adddate(dy,-1)) QTR,
date_add(dy,interval -3 month) Q_start,
adddate(dy,-1) Q_end
from (
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
) y
2.5. SQL Server
2.5.1. sql
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 datepart(q,dateadd(d,-1,dy)) QTR,
dateadd(m,-3,dy) Q_start,
dateadd(d,-1,dy) Q_end
from x
order by 1
3. 依据特定时间单位检索数据
3.1. DB2
3.2. MySQL
3.3. SQL
select ename
from emp
where monthname(hiredate) in ('February','December')
or dayname(hiredate) = 'Tuesday'
3.4. Oracle
3.5. PostgreSQL
select ename
from emp
where rtrim(to_char(hiredate,'month')) in ('february','december')
or rtrim(to_char(hiredate,'day')) = 'tuesday'
3.7. SQL Server
3.7.1. sql
select ename
from emp
where datename(m,hiredate) in ('February','December')
or datename(dw,hiredate) = 'Tuesday'
4. 比较特定的日期要素
4.1. DB2
4.1.1. sql
select a.ename ||
' was hired on the same month and weekday as '||
b.ename msg
from emp a, emp b
where (dayofweek(a.hiredate),monthname(a.hiredate)) =
(dayofweek(b.hiredate),monthname(b.hiredate))
and a.empno ＜ b.empno
order by a.ename
4.2. Oracle
4.3. PostgreSQL
4.4. SQL
select a.ename ||
' was hired on the same month and weekday as '||
b.ename as msg
from emp a, emp b
where to_char(a.hiredate,'DMON') =
to_char(b.hiredate,'DMON')
and a.empno ＜ b.empno
order by a.ename
4.5. MySQL
4.5.1. sql
select concat(a.ename,
' was hired on the same month and weekday as ',
b.ename) msg
from emp a, emp b
where date_format(a.hiredate,'%w%M') =
date_format(b.hiredate,'%w%M')
and a.empno ＜ b.empno
order by a.ename
4.6. SQL Server
4.6.1. sql
select a.ename +
' was hired on the same month and weekday as '+
b.ename msg
from emp a, emp b
where datename(dw,a.hiredate) = datename(dw,b.hiredate)
and datename(m,a.hiredate) = datename(m,b.hiredate)
and a.empno ＜ b.empno
order by a.ename
5. 识别重叠的日期区间
5.1. 基础数据
5.1.1. sql
select *
from emp_project
EMPNO ENAME PROJ_ID PROJ_START PROJ_END
----- ---------- ------- ----------- -----------
7782 CLARK 1 16-JUN-2005 18-JUN-2005
7782 CLARK 4 19-JUN-2005 24-JUN-2005
7782 CLARK 7 22-JUN-2005 25-JUN-2005
7782 CLARK 10 25-JUN-2005 28-JUN-2005
7782 CLARK 13 28-JUN-2005 02-JUL-2005
7839 KING 2 17-JUN-2005 21-JUN-2005
7839 KING 8 23-JUN-2005 25-JUN-2005
7839 KING 14 29-JUN-2005 30-JUN-2005
7839 KING 11 26-JUN-2005 27-JUN-2005
7839 KING 5 20-JUN-2005 24-JUN-2005
7934 MILLER 3 18-JUN-2005 22-JUN-2005
7934 MILLER 12 27-JUN-2005 28-JUN-2005
7934 MILLER 15 30-JUN-2005 03-JUL-2005
7934 MILLER 9 24-JUN-2005 27-JUN-2005
7934 MILLER 6 21-JUN-2005 23-JUN-2005
5.2. DB2
5.3. Oracle
5.4. PostgreSQL
5.5. SQL
select a.empno,a.ename,
'project '||b.proj_id||
' overlaps project '||a.proj_id as msg
from emp_project a,
emp_project b
where a.empno = b.empno
and b.proj_start ＞= a.proj_start
and b.proj_start ＜= a.proj_end
and a.proj_id != b.proj_id
5.6. MySQL
5.6.1. sql
select a.empno,a.ename,
concat('project ',b.proj_id,
' overlaps project ',a.proj_id) as msg
from emp_project a,
emp_project b
where a.empno = b.empno
and b.proj_start ＞= a.proj_start
and b.proj_start ＜= a.proj_end
and a.proj_id != b.proj_id
5.7. SQL Server
5.7.1. sql
select a.empno,a.ename,
'project '+b.proj_id+
' overlaps project '+a.proj_id as msg
from emp_project a,
emp_project b
where a.empno = b.empno
and b.proj_start ＞= a.proj_start
and b.proj_start ＜= a.proj_end
and a.proj_id != b.proj_id
6. 生成日历
6.1. DB2
6.1.1. sql
with x(dy,dm,mth,dw,wk)
as (
select (current_date -day(current_date) day +1 day) dy,
day((current_date -day(current_date) day +1 day)) dm,
month(current_date) mth,
dayofweek(current_date -day(current_date) day +1 day) dw,
week_iso(current_date -day(current_date) day +1 day) wk
from t1
union all
select dy+1 day, day(dy+1 day), mth,
dayofweek(dy+1 day), week_iso(dy+1 day)
from x
where month(dy+1 day) = mth
)
select max(case dw when 2 then dm end) as Mo,
max(case dw when 3 then dm end) as Tu,
max(case dw when 4 then dm end) as We,
max(case dw when 5 then dm end) as Th,
max(case dw when 6 then dm end) as Fr,
max(case dw when 7 then dm end) as Sa,
max(case dw when 1 then dm end) as Su
from x
group by wk
order by wk
6.2. Oracle
6.2.1. sql
with x
as (
select *
from (
select to_char(trunc(sysdate,'mm')+level-1,'iw') wk,
to_char(trunc(sysdate,'mm')+level-1,'dd') dm,
to_number(to_char(trunc(sysdate,'mm')+level-1,'d')) dw,
to_char(trunc(sysdate,'mm')+level-1,'mm') curr_mth,
to_char(sysdate,'mm') mth
from dual
connect by level ＜= 31
)
where curr_mth = mth
)
select max(case dw when 2 then dm end) Mo,
max(case dw when 3 then dm end) Tu,
max(case dw when 4 then dm end) We,
max(case dw when 5 then dm end) Th,
max(case dw when 6 then dm end) Fr,
max(case dw when 7 then dm end) Sa,
max(case dw when 1 then dm end) Su
from x
group by wk
order by wk
6.3. PostgreSQL
6.3.1. sql
select max(case dw when 2 then dm end) as Mo,
max(case dw when 3 then dm end) as Tu,
max(case dw when 4 then dm end) as We,
max(case dw when 5 then dm end) as Th,
max(case dw when 6 then dm end) as Fr,
max(case dw when 7 then dm end) as Sa,
max(case dw when 1 then dm end) as Su
from (
select *
from (
select cast(date_trunc('month',current_date) as date)+x.id,
to_char(
cast(
date_trunc('month',current_date)
as date)+x.id,'iw') as wk,
to_char(
cast(
date_trunc('month',current_date)
as date)+x.id,'dd') as dm,
cast(
to_char(
cast(
date_trunc('month',current_date)
as date)+x.id,'d') as integer) as dw,
to_char(
cast(
date_trunc('month',current_date)
as date)+x.id,'mm') as curr_mth,
to_char(current_date,'mm') as mth
from generate_series (0,31) x(id)
) x
where mth = curr_mth
) y
group by wk
order by wk
6.4. MySQL
6.4.1. sql
select max(case dw when 2 then dm end) as Mo,
max(case dw when 3 then dm end) as Tu,
max(case dw when 4 then dm end) as We,
max(case dw when 5 then dm end) as Th,
max(case dw when 6 then dm end) as Fr,
max(case dw when 7 then dm end) as Sa,
max(case dw when 1 then dm end) as Su
from (
select date_format(dy,'%u') wk,
date_format(dy,'%d') dm,
date_format(dy,'%w')+1 dw
from (
select adddate(x.dy,t500.id-1) dy,
x.mth
from (
select adddate(current_date,-dayofmonth(current_date)+1) dy,
date_format(
adddate(current_date,
-dayofmonth(current_date)+1),
'%m') mth
from t1
) x,
t500
where t500.id ＜= 31
and date_format(adddate(x.dy,t500.id-1),'%m') = x.mth
) y
) z
group by wk
order by wk
6.5. SQL Server
with x(dy,dm,mth,dw,wk)
as (
select dy,
day(dy) dm,
datepart(m,dy) mth,
datepart(dw,dy) dw,
case when datepart(dw,dy) = 1
then datepart(ww,dy)-1
else datepart(ww,dy)
end wk
from (
select dateadd(day,-day(getdate())+1,getdate()) dy
from t1
) x
union all
select dateadd(d,1,dy), day(dateadd(d,1,dy)), mth,
datepart(dw,dateadd(d,1,dy)),
case when datepart(dw,dateadd(d,1,dy)) = 1
then datepart(wk,dateadd(d,1,dy))-1
else datepart(wk,dateadd(d,1,dy))
end
from x
where datepart(m,dateadd(d,1,dy)) = mth
)
select max(case dw when 2 then dm end) as Mo,
max(case dw when 3 then dm end) as Tu,
max(case dw when 4 then dm end) as We,
max(case dw when 5 then dm end) as Th,
max(case dw when 6 then dm end) as Fr,
max(case dw when 7 then dm end) as Sa,
max(case dw when 1 then dm end) as Su
from x
group by wk
order by wk
