从日期开始的Oracle周数，但从12月1日开始

Question

好的，我有一个查询，它一直工作良好，计算从12月1日(我们的销售财政年度开始)开始的周数。

现在需求发生了变化。我仍然需要根据字段(Invoice_Date)计算周数。但是，现在我需要从离12月1日最近的星期一开始计数，而不是从12月1日开始计数(12月1日至7日，第1周等)。据我所知，ISO周正是我所期待的，但它从1月1日开始。我如何修改它，使它从12月1日开始工作?

Answer 1

select next_day(to_date('0112' || to_char(sysdate, 'YYYY'),'ddmmyyyy') - 1, 'MONDAY') dec_mon from dual;
给出当年12月的第一个星期一

周数只是ceil((sysdate - dec_mon)/7)。

如果你想要12月1日前的最后一个星期一，你可以在:

select next_day(to_date('2511' || to_char(sysdate, 'YYYY'),'ddmmyyyy') - 1, 'MONDAY') 
from dual;

Answer 2

在这个建议的解决方案中，我首先构建一个"帮助表"，显示每个财政年度的Monday_from和Monday_to(在第三个CTE中，名为ranges)。然后我构建了几个测试日期——我很懒，我应该使用to_date()，这样我也可以包含时间组件。实际解决方案中的连接条件(在代码末尾)是这样编写的，因此对于具有非零"时间"组件的日期无需修改即可工作。

我使用了Oracle 11.2的一个很好的特性，它允许我们在cte的声明中给出列别名——否则列别名将需要移动到各自的SELECT s中。否则，解决方案至少应该适用于Oracle 9及以上(我认为)。

with
     y ( dt ) as (
       select add_months(date '2000-12-01', 12 * level )
       from   dual
       connect by level <= 30
     ),
     m ( dt ) as (
       select trunc(dt, 'iw') + case when dt - trunc(dt, 'iw') <= 3 then 0 else 7 end
       from   y       
     ),
     ranges ( monday_from, monday_to ) as (
       select dt, lead(dt) over (order by dt) - 1
       from   m
     ),
     test_dates ( t_date ) as (
       select date '2013-02-23' from dual union all
       select date '2008-12-01' from dual union all
       select date '2008-04-28' from dual union all
       select date '2016-11-29' from dual
     )
select t_date, monday_from, 1 + trunc((t_date - monday_from)/7) as week_no
from   test_dates t inner join ranges r
       on t.t_date >= r.monday_from and t.t_date < r.monday_to
;
T_DATE              MONDAY_FROM            WEEK_NO
------------------- ------------------- ----------
2008-04-28 00:00:00 2007-12-03 00:00:00         22
2008-12-01 00:00:00 2008-12-01 00:00:00          1
2013-02-23 00:00:00 2012-12-03 00:00:00         12
2016-11-29 00:00:00 2016-11-28 00:00:00          1

Answer 3

使用以下函数返回离任意给定日期最近的星期一:

NEXT_DAY(some_date-4,'Monday')

如下所示:

with dts(some_date) as (
  select date '2006-12-1' from dual
  union all
  select add_months(some_date,12)
    from dts
   where some_date <= date '2014-12-1'
)
select some_date
     , next_day(some_date-4,'monday') nearest
     , some_date - next_day(some_date-4,'monday') dist
  from dts;
SOME_DATE   NEAREST           DIST
----------- ----------- ----------
01-DEC-2006 04-DEC-2006         -3
01-DEC-2007 03-DEC-2007         -2
01-DEC-2008 01-DEC-2008          0
01-DEC-2009 30-NOV-2009          1
01-DEC-2010 29-NOV-2010          2
01-DEC-2011 28-NOV-2011          3
01-DEC-2012 03-DEC-2012         -2
01-DEC-2013 02-DEC-2013         -1
01-DEC-2014 01-DEC-2014          0
01-DEC-2015 30-NOV-2015          1
 10 rows selected