数据库语法总结（9）——操作日期

今天本章继续更新日期相关的处理，上一张主要讲日期的计算，本章主要讲日期的操作

1、判断当前年份是否是闰年

这个的判断一直是有个热点话题，实现的方法有很多种，咱们主要根据上一章数据库语法总结（8）——计算日期的函数讲一下思路，以Oracle为例：

（1）判断年份是否可以整除4，可以整除就是闰年

SELECT (CASE 
MOD(to_number(to_char(SYSDATE,'yyyy')),4)--MOD为求余函数
WHEN 0 THEN '闰年' ELSE '平年' END) 年 
  FROM dual;

（2）找到当前年份的第二个月，判断最后一天，如果是2月29就是闰年

SELECT to_char(
    last_day(
        add_months(TRUNC(SYSDATE,'y'),1)
    )
,'DD') AS DAYS FROM dual;

2、找出当前年份一共有多少天、月份、星期等

（1）查找当前年份天数，就是找到当前年份的第一天，和下一年的第一天，计算二者之间的间隔，我们上一章相关函数介绍了（同样这里就只列一下Oracle的例子供大家参考）

SELECT add_months(trunc(sysdate,'y'),12)
    -trunc(sysdate,'y') AS days FROM dual;

（2）查找当前月份天数，查找当前月的第一天和下一个月第一天计算间隔

SELECT add_months(trunc(sysdate,'mm'),1)-
    trunc(sysdate,'mm') AS days FROM dual;

（3）查找一年中所有的星期几

例如查找星期五，首先先把当年所有的天数查询出来，然后查找星期五的数据

查询所有天数有多种方式

--借助系统表找出足够的天数长度，但是有风险，万一是闰年那就是366天<367
WITH x AS
(SELECT trunc(sysdate,'y')+rownum-1 dy
	FROM all_tables	WHERE rownum<366
)
--递归的方式返回当年的每一天
WITH x AS 
(
SELECT trunc(sysdate,'y')+LEVEL-1 dy
 FROM dual CONNECT BY LEVEL<=add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y')
)

--上面二选其一，然后查询对应的星期数
SELECT dy FROM x WHERE to_char(dy,'dy') ='星期五'--星期五要注意英文下是fri

（4）查找一个月中第一个和最后一个星期几

在Oracle中可以直接使用函数next_day和last_day运算 

 select next_day(trunc(sysdate,'mm')-1,'星期一') first_monday,
       next_day(last_day(trunc(sysdate,'mm'))-7,'星期一') last_monday
  from dual;

next_day求指定日期的下一个日期，语法就是next_day(data,weekdate)

对于其他的dbms来说，没有如此方便的函数，所以要运用上面提到的所有函数计算，我发现有些网站写的十分全面，我在这就不写了 

SQL 找出当前月份的第一个和最后一个星期一 (geek-docs.com)

3、创建日历

此处仍以Oracle为例：先使用connect by 返回当月的每一天，然后使用case和max合并一周的各天

  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;

参考：SQL 生成日历|极客教程 (geek-docs.com)

其他的数据库的思路都是差不多的，我们在这就扩展一下各个数据库的函数，方便大家理解

扩展：DB2：dayofweek(date)返回参数（日期、时间戳、日期字符串、时间戳字符串）在周内的天值（从周日开始1到7）

week_iso(date)返回参数在当年内的周值

PostgreSQL：generate_series(start, stop, step)生成一个数值序列，从start 到 stop，步进为step。step不写默认为1

date_trunc(类型,值)截断日期，与Oracle中的trunc功能类似，用法为date_trunc('month',now())

Mysql：date_format(date,format)用于以不同的格式显示日期/时间数据。

adddate()在指定的日期上进行修改，例如现在的日期加5天

Sqlserver：datepart(type,date)用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等

4、查找季度的第一天和最后一天

（1）列出一年中各个季度的第一天和最后一天

首先我们知道一共有4个季度，所以要生成四行数据，然后利用我们这两张讲的函数找出每个季度的第一天和下一个季度的第一天-1就可以了

Oracle

select rownum qtr,
        add_months(trunc(sysdate,'y'),(rownum-1)*3) q_start,
        add_months(trunc(sysdate,'y'),rownum*3)-1 q_end
   from T_DEMP_3 
  where rownum <= 4

DB2有返回季度的函数quarter()

   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 T_DEMP_3 
   fetch first 4 rows only
         ) x
         ) y

PostgreSQL使用 GENERATE_SERIES 函数生成所需的 4 个季度。使用 DATE_TRUNC 函数对每个季度的日期做截断处理，使之仅精确到年份和月份。使用 TO_CHAR 函数计算出每一对开始日期和结束日期分别属于哪个季度。

   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

MySQL使用 T500 表生成 4 行数据（每个季度一行）。使用 DATE_ADD 和 ADDDATE 函数计算出每个季度的开始日期和结束日期。使用 QUARTER 函数计算出每一对开始和结束日期分别属于哪个季度。

   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

SQL Server使用 WITH 递归查询生成 4 行数据。使用 DATEADD 函数找出开始日期和结束日期。使用 DATEPART 函数计算出每一对开始日期和结束日期分别属于哪个季度。

   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

（2）确定某个具体年份季度的第一天和最后一天

主要思路是使用substr函数从内嵌视图x里提取出年份，使用mod函数提取出对应的季度序号。Oracle、Mysql、PostgreSQL、DB2除了一些函数不同，写法都是类似的 

  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

Sqlserver略有不同，它没有提供对应的mod函数，要使用求模运算符%确定季度

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 t1 union all
select 20052 yrq from t1 union all
select 20053 yrq from t1 union all
select 20054 yrq from t1
       ) x
       ) y

扩展：mod(m,n)
(1)mod返回m除以n的余数，如果n是0，返回m。
(2)这个函数以任何数字数据类型或任何非数值型数据类型为参数，可以隐式地转换为数字数据类型。
Oracle用最高的数字优先级来确定参数，隐式地将剩下的参数转换为该数据类型，并返回数据类型。

5、根据入职时间统计每个月的招聘人员数量

我们需要计算出2020~2023年间，公司每个月招聘人员的数量,但是现在人员表emp的只记录了人员的入职时间hiredate，那要如何统计呢？

我们可以先生成2020~2023年间所有的月份，然后关联人员表emp进行统计，需要注意的是没有招聘员工的月份需要统计的数量为0。以Oracle为例

    with x
      as (
  select add_months(start_date,level-1) start_date
    from (
  select min(trunc(hiredate,'y')) start_date,
         add_months(max(trunc(hiredate,'y')),12) end_date
    from emp
         )
   connect by level <= months_between(end_date,start_date)
  )
  select x.start_date MTH, count(e.hiredate) num_hired
    from x, emp e
   where x.start_date = trunc(e.hiredate(+),'mm')
   group by x.start_date
   order by 1;

剩余的各种类型的数据库大家请参考SQL 填补缺失的日期 (geek-docs.com)

6、根据日期特定部分进行查找比较统计

还是上面提到的人员表emp和时间hiredate

（1） 根据星期几、月份查找出对应的日期，例如你想要查找2月份和3月份的星期二的数据

Oracle和PostgreSQL：

--中文数据库
select 1 AS a，rtrim(to_char(sysdate,'month'))
   from dual
  where rtrim(to_char(sysdate,'month')) in ('2月','3月')
     or rtrim(to_char(sysdate,'day')) = '星期二'
--英文状态下数据库
select 1 AS a，rtrim(to_char(sysdate,'month'))
   from dual
  where rtrim(to_char(sysdate,'month')) in ('February','March')
     or rtrim(to_char(sysdate,'day')) = 'tuesday'

DB2 和 MySQL:

select ename
  from emp
where monthname(hiredate) in ('February','December')
    or dayname(hiredate) = 'Tuesday'

Sqlserver:

select ename
 from emp
where datename(m,hiredate) in ('February','December')
 or datename(dw,hiredate) = 'Tuesday'

（2）根据月份、星期（不限年份）来查找相同的员工名称并连接成字符串

DB2：使用dayofweek函数返回一个数值表示星期几。使用monthname函数返回月份名称。

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

Oracle 和 PostgreSQL:使用to_char函数格式化日期得到筛选条件里的星期值和月份。

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

MySQL:使用date_format函数格式化日期得到筛选条件里的星期值和月份。

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

Sqlserver：使用datename函数格式化日期得到筛选条件里的星期值和月份。

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

我这发现有一位博主总结的很全面，给大家推荐一下，望共勉。（我也是边总结边学习哈） 

https://www.cnblogs.com/baidu-google/articles/4143799.html

好了，今天就先介绍到这了，有问题的地方可以私聊或者留言