SQL确定当前周是否为包含N个月份的周

Question

我正在寻找一些SQL，以确定当前星期是否是包含月份的第N天的星期。

例子。我想知道我是否处于包含本月将发生的第三个星期五的一周中。或者每月的哪一周包含第三个星期五。星期应定义为从星期日开始到星期六结束。因此，从周六开始的一个月要到第4周才看到星期五，而从周日-周五开始的一个月则是该月的第3周的第3个星期五。

不确定是否需要PL/SQL。

Answer 1

您可以简单地生成包含每月第N天的几周的日期范围，例如每个月的第三个星期五，查询如下:

select d - day_of_week AS sunday
      ,d + (7 - day_of_week) AS saturday
from   (select trunc(sysdate,'YY')+rownum-1 AS d
              ,to_number(to_char(trunc(sysdate,'YY')+rownum-1,'D'))
               AS day_of_week
        from dual connect by level <= 366)
where  to_char(d,'W') = 3
and    to_char(d,'DY') = 'FRI';

SUNDAY      SATURDAY
==========  ==========
14/01/2012  21/01/2012
11/02/2012  18/02/2012
10/03/2012  17/03/2012
14/04/2012  21/04/2012
12/05/2012  19/05/2012
09/06/2012  16/06/2012
14/07/2012  21/07/2012
11/08/2012  18/08/2012
15/09/2012  22/09/2012
13/10/2012  20/10/2012
10/11/2012  17/11/2012
15/12/2012  22/12/2012

编辑:
您可以创建一个简单的功能来检查单个日期，例如:

CREATE FUNCTION date_in_week_of_nth_day
   (in_date IN DATE
   ,in_week IN NUMBER
   ,in_day  IN VARCHAR2
   ) RETURN CHAR IS
  ret CHAR(1);
BEGIN
  select 'Y' into ret
  from   (select trunc(in_date,'MM')+rownum-1 AS d
                ,to_number(to_char(trunc(in_date,'MM')+rownum-1,'D'))
                 AS day_of_week
          from dual connect by level <= 31)
  where  to_char(d,'W') = in_week
  and    to_char(d,'DY') = in_day
  and    :in_date between (d - day_of_week) and (d + (7 - day_of_week));
  RETURN ret;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN 'N';
END;