Oracle中的SQL工作周 [英] SQL working week in Oracle

问题描述

我需要返回年份中工作"星期数的Oracle SQL:

I need Oracle SQL that returns the 'working' week number in year:

• 从一年到另一年没有几周
• 每个星期从星期一开始
• 一年中的前几天是第01周

所以结果应该是:

2015-12-28 - MON - week 53
2015-12-29 - TUE - week 53
2015-12-30 - WED - week 53
2015-12-31 - THU - week 53
===
2016-01-01 - FRI - week 01 - reseting yearly week counter
2016-01-02 - SAT - week 01
2016-01-03 - SUN - week 01
---
2016-01-04 - MON - week 02 - monday start of new week
2016-01-05 - TUE - week 02
...
2016-12-31 - SAT - week 53
===
2017-01-01 - SUN - week 01 - reseting yearly week counter
2017-01-02 - MON - week 02 - monday start of new week
...

推荐答案

我自己找到了答案，TO_CHAR(date,'IW')格式没有用，因为根据该标准(ISO)，一年中的第一周可以在新年，但也要过新年(请查看TO_CHAR(TO_DATE('2014-12-31','YYYY-MM-DD'),'IW')=01属于明年的第一周！)

I found the answer myself, TO_CHAR(date,'IW') format is of no use because the very first week in a year according to this standard (ISO) can start after the New Year but also before it (look at TO_CHAR(TO_DATE('2014-12-31','YYYY-MM-DD'),'IW')=01 the first week that belongs to the next year!)

           | DAY | WW | IW | MY
===========+=====+====+====+====
2014-12-28 | SUN | 52 | 52 | 52
2014-12-29 | MON | 52 | 01 | 53
2014-12-30 | TUE | 52 | 01 | 53
2014-12-31 | WED | 52 | 01 | 53
2015-01-01 | THU | 53 | 01 | 53
...        | ... | .. | .. | ..
2016-12-31 | THU | 53 | 53 | 01
2016-01-01 | FRI | 01 | 53 | 01
2016-01-02 | SAT | 01 | 53 | 01
2016-01-03 | SUN | 01 | 53 | 01
2016-01-04 | MON | 01 | 01 | 02
2016-01-05 | TUE | 01 | 01 | 02
2016-01-06 | WED | 01 | 01 | 02
2016-01-07 | THU | 01 | 01 | 02
2016-01-08 | FRI | 02 | 01 | 02

逻辑很简单，让我们看一下一年中的第一天以及它与星期一的偏移量.如果当前日期大于该第一天的偏移量，则周数应增加1.

The logic is quite simple, let's look at the very first day in year and its offset from monday. If current day is bigger than this first day offset then week number should be incremented by 1.

第一天的数量(从星期一开始的偏移量)通过以下方式计算:

The number of very first day (offset from monday) is calculated with:

TO_CHAR(TO_DATE(TO_CHAR(dt,'YYYY')||'0101','YYYYMMDD'),'D'))

所以最终的SQL语句是

So the final SQL statement is

WITH DATES AS
(
  SELECT DATE '2014-12-25' + LEVEL -1 dt FROM DUAL CONNECT BY LEVEL <= 500
)
SELECT dt,TO_CHAR(dt,'DY') DAY,TO_CHAR(dt,'WW') WW,TO_CHAR(dt,'IW') IW,
   CASE WHEN TO_CHAR(dt,'D')<TO_CHAR(TO_DATE(TO_CHAR(dt,'YYYY')||'0101','YYYYMMDD'),'D') THEN 
     LPAD(TO_CHAR(dt,'WW')+1,2,'0')
   ELSE 
     TO_CHAR(dt,'WW')
   END MY
FROM dates 

当然，可以为此目的创建一个函数，例如:

Of course, one can create a function for that purpose like:

CREATE OR REPLACE FUNCTION WorkingWeek(dt IN DATE) RETURN CHAR
IS
BEGIN
  IF(TO_CHAR(dt,'D')<TO_CHAR(TO_DATE('0101'||TO_CHAR(dt,'YYYY'),'DDMMYYYY'),'D')) THEN 
   RETURN LPAD(TO_CHAR(dt,'WW')+1,2,'0'); 
 ELSE 
   RETURN TO_CHAR(dt,'WW');
 END IF;
END WorkingWeek;
/

这篇关于Oracle中的SQL工作周的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！