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