SQL经典实例——操作日期

原创于 2026-06-18 14:47:44 发布 · 321 阅读

3 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

标签

#sql #数据库

SQL经典实例专栏收录该内容

15 篇文章

订阅专栏

操作日期

1、判断特定的年份是否是闰年

问题：你想判断当前年份是否是闰年。

解决方案：如果你使用 SQL 已有一段时间，那么肯定知道解决这个问题有多种方法。我们见过的所有解决方案的效果都很好，但这里介绍的解决方案可能是最简单的。下面的解决方案会检查 2 月份的最后一天，如果它是 2 月 29 日，那么当前年份就是闰年。

DB2：使用递归式 WITH 子句返回 2 月份的每一天，然后使用聚合函数 MAX 找出 2 月份的最后一天。

   with x (dy,mth)
     as (
 select dy, month(dy)
   from (
 select (current_date -
          dayofyear(current_date) days +1 days)
           +1 months as dy
   from t1
        ) tmp1
  union all
 select dy+1 days, mth
   from x
  where month(dy+1 day) = mth
 )
 select max(day(dy))
   from x;

Oracle：使用函数 LAST_DAY 找出 2 月份的最后一天。

select to_char(
         last_day(add_months(trunc(sysdate,'y'),1)),
        'DD')
  from t1;

PostgreSQL：使用函数 GENERATE_SERIES 返回 2 月份的每一天，然后使用聚合函数 MAX 找出 2 月份的最后一天。

select max(to_char(tmp2.dy+x.id,'DD')) as dy
from (
	select dy, to_char(dy,'MM') as mth
	from (
		select cast(cast(date_trunc('year',current_date) as date)
			+ interval '1 month' as date) as dy
		from t1
	) tmp1
) tmp2, generate_series (0,29) x(id)
where to_char(tmp2.dy+x.id,'MM') = tmp2.mth;

 dy 
----
 28
(1 row)

MySQL：使用函数 LAST_DAY 找出 2 月份的最后一天。

select day(
       last_day(
       date_add(
       date_add(
       date_add(current_date,
                interval -dayofyear(current_date) day),
                interval 1 day),
                interval 1 month))) dy
  from t1;

在这里插入图片描述
SQL Server：使用函数 DAY 检查当前年份是否有 2 月 29 日，如果没有，那么这个函数将返回 NULL。如果函数 DAY 的返回值不是 NULL，那么函数 COALESCE 将返回这个值，否则返回 28。

select coalesce
	(day
		(cast(concat
			(year(getdate()),'-02-29') as date)),28);

2、确定特定年份有多少天

问题：你想计算当前年份有多少天。

解决方案：当前年份的天数，就是下一年第一天与当前年份第一天之间相差的天数。所有解决方案都采取如下步骤。

找出当前年份的第一天。
给上面的日期加上 1 年，得到下一年的第一天。
将前两步得到的日期相减。

在不同的 RDBMS 中，解决方案之间的唯一差别在于，用来执行这些步骤的内置函数不同。

DB2：使用函数 DAYOFYEAR 找出当前年份的第一天，并使用DAYS 确定两个日期之间相隔的天数。

select days((curr_year + 1 year)) - days(curr_year)
  from (
select (current_date -
        dayofyear(current_date) day +
         1 day) curr_year
  from t1
       ) x;

Oracle：使用函数 TRUNC 找出当前年份的第一天，然后使用ADD_MONTHS 找出下一年的第一天。

select add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y')
from dual

PostgreSQL：使用函数 DATE_TRUNC 找出当前年份的第一天，然后使用间隔算术运算确定下一年的第一天。

select cast((curr_year + interval '1 year') as date) - curr_year
from (
	select cast(date_trunc('year',current_date) as date) as curr_year
  from t1
) x;

 ?column? 
----------
      365
(1 row)

MySQL：使用 ADDDATE 找出当前年份的第一天，然后使用DATEDIFF 和间隔算术运算确定当前年份的天数。

select datediff(d,curr_year,dateadd(yy,1,curr_year))
from (
	select dateadd(d,-datepart(dy,getdate())+1,getdate()) curr_year
  from t1
) x;

SQL Server：使用函数 DATEADD 找出当前年份的第一天，然后使用DATEDIFF 返回当前年份的天数。

select datediff(d,curr_year,dateadd(yy,1,curr_year))
  from (
select dateadd(d,-datepart(dy,getdate())+1,getdate()) curr_year
  from t1
       ) x;

3、提取日期的各个组成部分

问题：你想将当前日期拆分成 6 部分：日、月、年、秒、分钟和小时，并将结果作为数字返回。

解决方案：这里处理的是当前日期，但这些解决方案也适用于其他日期。当前，大多数 RDBMS 实现了 ANSI 标准定义的日期部分提取函数 EXTRACT，但 SQL Server 是个例外。这些 RDBMS 同时保留了提取日期部分的遗留方法。

DB2：DB2 实现了一系列内置函数，让你能够轻松地提取日期的不同部分。函数 HOUR、MINUTE、SECOND、DAY、MONTH 和 YEAR 会分别返回日期的相应部分。（如果要提取日，可以使用 DAY；如果要提取小时，可以使用HOUR。以此类推。）

        select    hour( current_timestamp ) hr,
                minute( current_timestamp ) min,
                second( current_timestamp ) sec,
                   day( current_timestamp ) dy,
                 month( current_timestamp ) mth,
                   year( current_timestamp ) yr
          from t1

lect

        tract(hour from current_timestamp)
      extract(minute from current_timestamp
      extract(second from current_timestamp)
      extract(day from current_timestamp)
      extract(month from current_timestamp)
      extract(year from current_timestamp)

             MIN   SEC    DY   MTH    YR
        -- ----- ----- ----- ----- -----
              28    36    15     6  2005

Oracle：使用函数 TO_CHAR 和 TO_NUMBER 提取日期的特定部分。

 select to_number(to_char(sysdate,'hh24')) hour,
        to_number(to_char(sysdate,'mi')) min,
        to_number(to_char(sysdate,'ss')) sec,
        to_number(to_char(sysdate,'dd')) day,
        to_number(to_char(sysdate,'mm')) mth,
        to_number(to_char(sysdate,'yyyy')) year
  from dual

  UR   MIN   SEC   DAY   MTH  YEAR
  -- ----- ----- ----- ----- -----
        28    36    15     6  2005

PostgreSQL：使用函数 TO_CHAR 和 TO_NUMBER 提取日期的特定部分。

select to_number(to_char(current_timestamp,'hh24'),'99') as hr,
       to_number(to_char(current_timestamp,'mi'),'99') as min,
       to_number(to_char(current_timestamp,'ss'),'99') as sec,
       to_number(to_char(current_timestamp,'dd'),'99') as day,
       to_number(to_char(current_timestamp,'mm'),'99') as mth,
       to_number(to_char(current_timestamp,'yyyy'),'9999') as yr
from t1;

 hr | min | sec | day | mth |  yr  
----+-----+-----+-----+-----+------
 13 |  15 |   5 |  18 |   6 | 2026
(1 row)

MySQL：使用函数 DATE_FORMAT 提取日期的特定部分。

select date_format(current_timestamp,'%k') hr,
       date_format(current_timestamp,'%i') min,
       date_format(current_timestamp,'%s') sec,
       date_format(current_timestamp,'%d') dy,
       date_format(current_timestamp,'%m') mon,
       date_format(current_timestamp,'%Y') yr
from t1;

在这里插入图片描述

4、找出一个月的第一天和最后一天

问题：你想找出当前月份的第一天和最后一天。

解决方案：下面介绍的解决方案找出了当前月份的第一天和最后一天，但只要稍做调整，就可用于找出任何月份的第一天和最后一天。

DB2：使用函数 DAY 确定当前日期是当前月份的第几天，然后将当前日期减去这个天数并加 1，得到当前月份的第一天。为了得到当前月份的最后一天，给当前日期加上 1 个月，再减去将函数 DAY 应用于当前日期加 1 个月时返回的天数。

select (date(current_date) - day(date(current_date)) day + 1 day) firstday,
       (date(current_date)+1 month
        - day(date(current_date)+1 month) day) lastday
  from t1;

Oracle：使用函数 TRUNC 找出月份的第一天，并使用函数LAST_DAY 找出月份的最后一天。

select trunc(sysdate,'mm') firstday,
       last_day(sysdate) lastday
from dual;

PostgreSQL：使用函数 DATE_TRUNC 将当前日期截断为当前月份的第一天。有了月份的第一天后，只需加上 1 个月再减去 1天，就可找出该月份的最后一天。

select firstday,
	cast(firstday + interval '1 month' - interval '1 day' as date) as lastday
from (
	select cast(date_trunc('month',current_date) as date) as firstday
	from t1
) x;

  firstday  |  lastday   
------------+------------
 2026-06-01 | 2026-06-30
(1 row)

MySQL：使用函数 DAY 找出当前日期是其所属月份的第几天，然后使用函数 DATE_ADD 将当前日期减去这个天数并加1，得到该月份的第一天。要找出当前月份的最后一天，可以使用函数 LAST_DAY。

select date_add(current_date,interval-day(current_date)+1 day) firstday,
			 last_day(current_date) lastday
from t1;

在这里插入图片描述
SQL Server：使用函数 DAY 找出当前日期是其所属月份的第几天，然后使用函数 DATEADD 将当前日期减去这个天数并加 1，得到该月份的第一天。要找出该月份的最后一天，也可以使用函数 DAY 和 DATEADD，方法是给当前日期加 1 个月得到一个新日期，并计算该新日期是其所属月份的第几天，再将新日期减去这个天数。

select dateadd(day,-day(getdate())+1,getdate()) firstday,
       dateadd(day,
               -day(dateadd(month,1,getdate())),
               dateadd(month,1,getdate())) lastday
from t1;

5、找出一年中所有的星期n

问题：你想找出一年中所有为星期 n 的日期。例如，你可能想列出当前年份中所有的星期五。

解决方案：无论使用的是哪个 RDBMS，解决方案的关键都是返回当前年份的每一天，然后只保留那些为星期 n 的日期。下面的解决方案会保留所有的星期五。
DB2：使用递归式 WITH 子句返回当年的每一天，然后使用函数DAYNAME 将星期五对应的那些日期留下。

   with x (dy,yr)
     as (
 select dy, year(dy) yr
   from (
 select (current_date -
          dayofyear(current_date) days +1 days) as dy
   from t1
        ) tmp1
  union all
  select dy+1 days, yr
    from x
   where year(dy +1 day) = yr
  )
  select dy
    from x
 where dayname(dy) = 'Friday';

Oracle：使用递归式 CONNECT BY 子句返回当年的每一天，然后使用函数 TO_CHAR 将星期五对应的那些日期留下。

   with x
     as (
 select trunc(sysdate,'y')+level-1 dy
   from t1
   connect by level <=
      add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y')
 )
 select *
   from x
   where to_char( dy, 'dy') = 'fri';

PostgreSQL：使用递归式 CTE 生成相应年份的每一天，并剔除那些不是星期五的日期。该解决方案使用的是 ANSI 标准函数EXTRACT，因此适用于众多不同的 RDBMS。

with recursive cal (dy)
as (
	select current_date-(cast(extract(doy from current_date) as integer)-1)
	union all
	select dy+1
	from cal
	where extract(year from dy)=extract(year from (dy+1))
)
select dy,extract(dow from dy) 
from cal
where cast(extract(dow from dy) as integer) = 5;


    dy     | extract 
------------+---------
 2026-01-02 |       5
 2026-01-09 |       5
 2026-01-16 |       5
 2026-01-23 |       5
 2026-01-30 |       5
 2026-02-06 |       5
 2026-02-13 |       5
 2026-02-20 |       5
 2026-02-27 |       5
 2026-03-06 |       5
 2026-03-13 |       5
 2026-03-20 |       5
 2026-03-27 |       5
 2026-04-03 |       5
 2026-04-10 |       5
 2026-04-17 |       5
 2026-04-24 |       5
 2026-05-01 |       5
 2026-05-08 |       5
 2026-05-15 |       5
 2026-05-22 |       5
 ...

MySQL：使用递归式 CTE 找出指定年份的每一天，然后剔除不是星期五的日期。

         with recursive cal (dy,yr)
   as
     (
     select dy, extract(year from dy) as yr
   from
     (select adddate
             (adddate(current_date, interval - dayofyear(current_date)
   day), interval 1 day) as dy) as tmp1
   union all
      select date_add(dy, interval 1 day), yr
   from cal
   where extract(year from date_add(dy, interval 1 day)) = yr
   )
      select dy from cal
      where dayofweek(dy) = 6;

SQL Server：使用递归式 WITH 子句返回当年的每一天，然后使用函数DAYNAME 将星期五对应的那些日期留下。

   with x (dy,yr)
     as (
 select dy, year(dy) yr
   from (
 select getdate()-datepart(dy,getdate())+1 dy
   from t1
        ) tmp1
  union all
 select dateadd(dd,1,dy), yr
   from x
  where year(dateadd(dd,1,dy)) = yr
 )
 select x.dy
   from x
  where datename(dw,x.dy) = 'Friday'
 option (maxrecursion 400);

6、找出一个月中第一个和最后一个星期n

问题：假如你想找出当月的第一个和最后一个星期一。

解决方案：下面的解决方案找出了当月的第一个和最后一个星期一，它们也可用于找出任何月份的第一个和最后一个星期 n。由于两个相邻的星期 n 之间相隔 7 天，因此找出第一个星期 n 后，加上 7 天可以找出第二个星期 n，加上 14 天可以找出第三个星期 n。同样，有了特定月份的最后一个星期 n 后，减去 7 天可以找出第三个星期 n，减去 14 天可以找出第二个星期 n。

DB2：使用递归式 WITH 子句生成当月的每一天，并使用 CASE表达式标出所有的星期一。在被标记的日期中，最早和最晚的将分别是第一个和最后一个星期一。

    with x (dy,mth,is_monday)
      as (
 select dy,month(dy),
        case when dayname(dy)='Monday'
              then 1 else 0
        end
   from (
 select (current_date-day(current_date) day +1 day) dy
   from t1
        ) tmp1
  union all
 select (dy +1 day), mth,
        case when dayname(dy +1 day)='Monday'
             then 1 else 0
        end
   from x
  where month(dy +1 day) = mth
 )
 select min(dy) first_monday, max(dy) last_monday
   from x
  where is_monday = 1;

Oracle：结合使用函数 NEXT_DAY 和 LAST_DAY 以及巧妙的日期算术运算，找出当月的第一个和最后一个星期一。

select next_day(trunc(sysdate,'mm')-1,'MONDAY') first_monday,
       next_day(last_day(trunc(sysdate,'mm'))-7,'MONDAY') last_monday
  from dual

PostgreSQL：使用函数 DATE_TRUNC 找出当月的第一天。有了当月的第一天后，要找出当月的第一个和最后一个星期一，可以使用简单的算术运算，这些运算涉及一周各天对应的数字（星期日到星期六分别对应数字 1~7）。

select first_monday,
        case to_char(first_monday+28,'mm')
             when mth then first_monday+28
						 else first_monday+21
						 end as last_monday
from (
	select case sign(cast(to_char(dy,'d') as integer)-2)
             when 0
             then dy
             when -1
             then dy+abs(cast(to_char(dy,'d') as integer)-2)
             when 1
             then (7-(cast(to_char(dy,'d') as integer)-2))+dy
        end as first_monday,
        mth
	from (
		select cast(date_trunc('month',current_date) as date) as dy,
        to_char(current_date,'mm') as mth
		from t1
	) x
) y;

 first_monday | last_monday 
--------------+-------------
 2026-06-01   | 2026-06-29
(1 row)

MySQL：使用函数 ADDDATE 找出当月的第一天。有了当月的第一天后，要找出当月的第一个和最后一个星期一，可以使用简单的算术运算，这些运算涉及一周各天对应的数字（星期日到星期六分别对应数字 1~7）。

 select first_monday,
        case month(adddate(first_monday,28))
             when mth then adddate(first_monday,28)
                      else adddate(first_monday,21)
        end last_monday
from (
	select case sign(dayofweek(dy)-2)
             when 0 then dy
             when -1 then adddate(dy,abs(dayofweek(dy)-2))
             when 1 then adddate(dy,(7-(dayofweek(dy)-2)))
        end first_monday,
        mth
	from (
		select adddate(adddate(current_date,-day(current_date)),1) dy,
        month(current_date) mth
		from t1
	) x
) y;

在这里插入图片描述
SQL Server：使用递归式 WITH 子句生成当月的每一天，然后使用CASE 表达式标出所有的星期一。在标出的日期中，最早和最晚的分别是第一个和最后一个星期一。

   with x (dy,mth,is_monday)
     as (
 select dy,mth,
        case when datepart(dw,dy) = 2
             then 1 else 0
        end
   from (
 select dateadd(day,1,dateadd(day,-day(getdate()),getdate())) dy,
        month(getdate()) mth
   from t1
        ) tmp1
  union all
 select dateadd(day,1,dy),
        mth,
        case when datepart(dw,dateadd(day,1,dy)) = 2
             then 1 else 0
        end
   from x
  where month(dateadd(day,1,dy)) = mth
 )
 select min(dy) first_monday,
        max(dy) last_monday
   from x
  where is_monday = 1;

7、创建日历

问题：你想创建当月的日历。该日历的格式应该与你桌上摆着的日历相同：包含沿水平方向排列的 7 列和沿垂直方向排列的 5 行。

解决方案：下面的每种解决方案都稍有不同，但解决问题的方法相同：返回当月的每一天，然后将每周的各天合并成一行以创建日历。

日历有多种格式。例如，Unix 命令 CAL 生成的日历以从星期日到星期六的顺序排列一周的各天。这里的解决方案基于 ISO 周，生成的日历将一周各天以从星期一到星期日的顺序排列。熟悉这些解决方案后，你将发现，要调整日历的格式，只需在转置前调整 ISO 周指定的值。

DB2：使用递归式 WITH 子句返回当月的每一天，然后使用CASE 和 MAX 合并一周的各天。

   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;

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;

PostgreSQL：使用函数 GENERATE_SERIES 返回当月的每一天，然后使用 CASE 和 MAX 合并一周的各天。

 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;



 mo | tu | we | th | fr | sa | su 
----+----+----+----+----+----+----
 01 | 02 | 03 | 04 | 05 | 06 | 07
 08 | 09 | 10 | 11 | 12 | 13 | 14
 15 | 16 | 17 | 18 | 19 | 20 | 21
 22 | 23 | 24 | 25 | 26 | 27 | 28
 29 | 30 |    |    |    |    | 
(5 rows)

MySQL：使用递归 CTE 返回当月的每一天，然后使用 CASE 和MAX 合并一周的各天。

with recursive 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 date_add(day,-day(getdate())+1,getdate()) dy
   from t1
        ) x
 union all
 select dateadd(d,1,dy), day(date_add(d,1,dy)), mth,
        datepart(dw,dateadd(d,1,dy)),
        case when datepart(dw,date_add(d,1,dy)) = 1
             then datepart(wk,date_add(d,1,dy))-1
             else datepart(wk,date_add(d,1,dy))
        end
   from x
  where datepart(m,date_add(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;

SQL Server：使用递归式 WITH 子句返回当月的每一天，然后使用CASE 和 MAX 合并一周的各天。

   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

8、列出一年中各个季度的第一天和最后一天

问题：你想返回给定年份中各个季度的第一天和最后一天。

解决方案：一年有 4 个季度，因此需要生成 4 行数据。生成所需的行数后，只需使用 RDBMS 提供的日期函数来返回各个季度的第一天和最后一天。你的目标是生成如下结果集（这里使用的是当年，也可以使用任何年份）。

DB2：使用 EMP 表和窗口函数 ROW_NUMBER OVER 来生成 4行数据。也可以像很多实例那样使用 WITH 子句来生成行，或者查询任何至少包含 4 行的表。下面的解决方案使用了窗口函数 ROW_NUMBER OVER。

 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;

Oracle：使用函数 ADD_MONTHS 找出各个季度的第一天和最后一天。使用 ROWNUM 表示季度编号。下面的解决方案使用EMP 表来生成 4 行数据。

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;

PostgreSQL：找出当前日期所属年份的第一天，并使用递归 CTE 填充其他 3 个季度的第一天，再找出每个季度的最后一天。

with recursive x (dy,cnt) as (
	select current_date -cast(extract(day from current_date)as integer) +1 dy, id
  from t1
	union all
	select cast(dy + interval '3 months' as date) , cnt+1
	from x
  where cnt+1 <= 4
)
select cast(dy - interval '3 months' as date) as Q_start, dy-1 as Q_end
from x;

  q_start   |   q_end    
------------+------------
 2026-03-01 | 2026-05-31
 2026-06-01 | 2026-08-31
 2026-09-01 | 2026-11-30
 2026-12-01 | 2027-02-28
(4 rows)

MySQL：找出当前日期所属年份的第一天，并使用 CTE 生成 4 行数据，每个季度 1 行。使用 ADDDATE 找出每个季度的最后一天（前一个季度的最后一天加上 3 个月或后一个季度的第一天减去 1 天）。

with recursive x (dy,cnt) as (
	select adddate(current_date,(-dayofyear(current_date))+1) dy,id
	from t1
	union all
	select adddate(dy, interval 3 month ), cnt+1
	from x
	where cnt+1 <= 4
)
select quarter(adddate(dy,-1)) QTR,  
		   date_add(dy, interval -3 month) Q_start,  
			 adddate(dy,-1) Q_end
from x
order by 1;

在这里插入图片描述
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;

9、确定给定季度的第一天和最后一天

问题：在以 YYYYQ 格式（4 位的年份和 1 位的季度）给定年份和季度的情况下，你想返回该季度的第一天和最后一天。

解决方案:本解决方案的关键在于使用求模函数根据 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 确定季度。

 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;

Oracle：使用函数 SUBSTR 从内嵌视图 X 返回的结果中提取年份，并使用函数 MOD 确定季度。

 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;

PostgreSQL：使用函数 SUBSTR 从内嵌视图 X 返回的结果中提取年份，并使用函数 MOD 确定季度。

 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;

MySQL：使用函数 SUBSTR 从内嵌视图 X 返回的结果中提取年份，并使用函数 MOD 确定季度。

 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;

SQL Server：使用函数 SUBSTRING 从内嵌视图 X 返回的结果中提取年份，并使用求模运算符 % 确定季度。

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

10、补全缺失的日期

问题：对于给定时间范围内的每个日期（每个月、每周或每年），你都要生成一行数据。例如，对于所有聘请了员工的年份，你要计算每个月聘请的员工数量。从所有员工的获聘日期可知，在 2000 年到 2003 年期间，每一年都聘请了员工。

select distinct
       extract(year from hiredate) as year
  from emp

YEAR
-----
2000
2001
2002
2003

你想计算从 2000 年到 2003 年，每个月聘请的员工数量。下面显示了要返回的结果集的一部分。

MTH          NUM_HIRED
----------- ----------
01-JAN-2001          0
01-FEB-2001          2
01-MAR-2001          0
01-APR-2001          1
01-MAY-2001          1
01-JUN-2001          1
01-JUL-2001          0
01-AUG-2001          0
01-SEP-2001          2
01-OCT-2001          0
01-NOV-2001          1
01-DEC-2001          2

解决方案：这里比较棘手的地方是，对于那些没有聘请员工的月份，也需要返回相应的行，但其中的员工聘请数量为 0。由于在 2000 年到 2003 年期间，并非每个月都聘请了员工，因此你必须生成所有的月份，然后基于 HIREDATE 外连接到 EMP 表（将 HIREDATE 截断到月份，使其能够与生成的月份匹配）。

DB2：使用递归式 WITH 子句生成每个月份（从 2000 年 1 月 1日到 2003 年 12 月 31 日的每个月份的第一天）。有了指定时间范围内的每个月份后，外连接到 EMP 表，并使用聚合函数 COUNT 计算每个月聘请的员工数量。

   with x (start_date,end_date)
     as (
 select (min(hiredate)
          dayofyear(min(hiredate)) day +1 day) start_date,
        (max(hiredate)
          dayofyear(max(hiredate)) day +1 day) +1 year end_date
   from emp
  union all
 select start_date +1 month, end_date
   from x
  where (start_date +1 month) < end_date
 )
 select x.start_date mth, count(e.hiredate) num_hired
   from x left join emp e
     on (x.start_date = (e.hiredate-(day(hiredate)-1) day))
  group by x.start_date
  order by 1;

Oracle：使用 CONNECT BY 子句生成 2000 年到 2003 年期间的每个月份，然后外连接到 EMP 表，并使用聚合函数COUNT 计算每个月聘请的员工数量。

   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 left join emp e
     on (x.start_date = trunc(e.hiredate,'mm'))
  group by x.start_date
  order by 1;

PostgreSQL：使用 CTE 生成从最早聘请日期开始的月份，然后根据年份和月份左外连接到 EMP 表，以计算每个月聘请的员工数量。

with recursive x (start_date, end_date) as (
    select cast(min(hiredate) - (cast(extract(day from min(hiredate))as integer) - 1) as date), max(hiredate)
    from emp
		union all
    select cast(start_date + interval '1 month' as date), end_date
    from x
    where start_date < end_date
 )
select x.start_date,count(hiredate)
from x left join emp
on (extract(month from start_date) = extract(month from emp.hiredate)
	and extract(year from start_date) = extract(year from emp.hiredate))
group by x.start_date
order by 1;

 start_date | count 
------------+-------
 2006-02-01 |     2
 2006-03-01 |     0
 2006-04-01 |     0
 2006-05-01 |     1
 2006-06-01 |     1
 2006-07-01 |     0
 2006-08-01 |     0
 2006-09-01 |     2
 2006-10-01 |     0
 2006-11-01 |     2
 2006-12-01 |     1
 2007-01-01 |     1
 2007-02-01 |     0
(13 rows)

MySQL：使用递归式 CTE 生成起始日期和终止日期之间的所有月份，然后连接到 EMP 表，以计算每个月聘请的员工数量。

with recursive x (start_date,end_date)
            as
           (
     select
         adddate(min(hiredate),
         -dayofyear(min(hiredate))+1)  start_date
         ,adddate(max(hiredate),
         -dayofyear(max(hiredate))+1)  end_date
         from emp
      union all
         select date_add(start_date,interval 1 month)
         , end_date
         from x
         where date_add(start_date, interval 1 month) < end_date
     )

      select x.start_date mth, count(e.hiredate) num_hired
             from x left join emp e
             on (extract(year_month from start_date)
                 =
                 extract(year_month from e.hiredate))
             group by x.start_date
             order by 1;

SQL Server：使用递归式 WITH 子句生成每个月份（从 2000 年 1 月 1日到 2003 年 12 月 31 日的每个月份的第一天）。有了指定时间范围内的所有月份后，外连接到 EMP 表，并使用聚合函数 COUNT 计算每个月聘请的员工数量。

   with x (start_date,end_date)
     as (
 select (min(hiredate) -
          datepart(dy,min(hiredate))+1) start_date,
        dateadd(yy,1,
         (max(hiredate) -
          datepart(dy,max(hiredate))+1)) end_date
   from emp
  union all
  select dateadd(mm,1,start_date), end_date
    from x
   where dateadd(mm,1,start_date) < end_date
  )
  select x.start_date mth, count(e.hiredate) num_hired
    from x left join emp e
      on (x.start_date =
             dateadd(dd,-day(e.hiredate)+1,e.hiredate))
  group by x.start_date
  order by 1;

11、根据日期的特定部分进行查找

问题：你想查找与给定月份、星期 n 或其他时间单位匹配的日期。例如，你想找出获聘日期为 2 月份或 12 月份以及为星期二的员工。

解决方案：使用 RDBMS 提供的函数确定日期所属的月份或星期 n。在很多场景下，这里介绍的解决方案很有用。假设你要根据 HIREDATE 进行检索，但想忽略年份（只提取月份或HIREDATE 的其他部分），就可以使用这里的解决方案。

这些解决方案根据日期所属的月份或为星期 n 进行检索。如果你熟悉 RDBMS 提供的日期函数，则可以轻松地修改这些解决方案，以根据年份、季度、年份和季度组合、月份和年份组合等进行检索。

DB2 和 MySQL：使用函数 MONTHNAME 和 DAYNAME 分别确定员工获聘日期所属月份以及为星期几。

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

Oracle 和 PostgreSQL：使用函数 TO_CHAR 确定员工获聘日期所属月份以及为星期几，使用函数 RTRIM 删除末尾的空白。

select ename
from emp
where rtrim(to_char(hiredate,'month')) in ('february','december')
	or rtrim(to_char(hiredate,'day')) = 'tuesday';

 ename  
--------
 ALLEN
 WARD
 JAMES
 MILLER
(4 rows)

SQL Server：使用函数 DATENAME 确定员工获聘日期所属月份以及为星期几。

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

12、根据日期的特定部分对记录进行比较

问题：你想查找哪些员工的获聘日期属于同一个月份且所属的星期 n 相同。如果一位员工的获聘日期为 2008 年 3 月 10日（星期一），而另一位员工的获聘日期为 2001 年 3 月2 日（星期一），那么就认为他们匹配，因为所属的月份和星期 n 都相同。在 EMP 表中，只有 3 位员工满足这些条件，而你想返回下面的结果集。

MSG
------------------------------------------------------
JAMES was hired on the same month and weekday as FORD
SCOTT was hired on the same month and weekday as JAMES
SCOTT was hired on the same month and weekday as FORD

解决方案:由于要对任意两位员工的 HIREDATE 进行比较，因此需要自连接 EMP 表。这将提供各种可能的 HIREDATE 组合以进行比较。自连接后，只需从每个 HIREDATE 中提取月份和星期 n，并进行比较。

DB2：自连接 EMP 表后，使用函数 DAYOFWEEK 返回日期所属星期 n 的数字表示，使用函数 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：自连接 EMP 表后，使用函数 TO_CHAR 将 HIREDATE 格式化为星期 n 和月份，以便进行比较。

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;

                         msg                          
------------------------------------------------------
 YODA was hired on the same month and weekday as KING
(1 row)

MySQL：自连接 EMP 表后，使用函数 DATE_FORMAT 将HIREDATE 格式化为星期 n 和月份，以便进行比较。

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;

SQL Server：自连接 EMP 表后，使用函数 DATENAME 将 HIREDATE格式化为星期 n 和月份，以便进行比较。

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;

13、找出重叠的日期范围

问题：你想找出所有这样的情况，即员工的当前项目还未结束就开始了下一个项目。请看下面的 EMP_PROJECT 表。

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

从与 KING 相关的结果可知，他在 PROJ_ID 5 结束前就开始了 PROJ_ID 8，并且在开始 PROJ_ID 5 的时候，PROJ_ID 2 还没有结束。你想返回如下结果集。

EMPNO ENAME      MSG
----- ---------- --------------------------------
7782  CLARK      project 7 overlaps project 4
7782  CLARK      project 10 overlaps project 7
7782  CLARK      project 13 overlaps project 10
7839  KING       project 8 overlaps project 5
7839  KING       project 5 overlaps project 2
7934  MILLER     project 12 overlaps project 9
7934  MILLER     project 6 overlaps project 3

解决方案：这里的关键是找出这样的行，即其 PROJ_START（新项目开始日期）不早于另一个项目的 PROJ_START，同时不晚于该项目的 PROJ_END。为此，需要能够对同一位员工参与的任意两个项目进行比较。通过以员工号相同的方式自连接 EMP_PROJECT 表，可以生成每位员工参与的任意两个项目的组合。要找出时间重叠的项目，只需找出这样的项目：其 PROJ_START 落在相应员工参与的另一个项目的 PROJ_START 和 PROJ_END 之间。

DB2、PostgreSQL 和 Oracle：自连接 EMP_PROJECT 表，然后使用拼接运算符 || 生成消息，指出两个项目的时间是重叠的。

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;

MySQL：自连接 EMP_PROJECT 表，然后使用函数 CONCAT 生成消息，指出两个项目的时间是重叠的。

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;

SQL Server：自连接 EMP_PROJECT 表，然后使用拼接运算符 + 生成消息，指出两个项目的时间是重叠的。

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;