SQL working week in Oracle

Question

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

no overflowing weeks from one year to another
each week starts from monday
first few days in year are week 01

So the result should be:

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
...

Possible duplicate of [Oracle week calculation issue](http://stackoverflow.com/questions/2283663/oracle-week-calculation-issue) — Jan, Feb 17 '16 at 08:10
@Jan No, it is not at all a duplicate. This topic is much more complicated than that. See the answer below. — Lalit Kumar B, Feb 17 '16 at 09:29

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

0

W - week number in a month

WW - week number in a year, week 1 starts at 1st of Jan

IW - week number in a year, according to ISO standard

For your requirement, you need to use combination of IW and WW format. You could combine them using a CASE expression.

If you want to generate the list of dates for entire year, then you could use the row generator method.

SQL> WITH sample_data AS(
  2  SELECT DATE '2015-12-28'    + LEVEL -1 dt FROM dual
  3  CONNECT BY LEVEL <= 15
  4  )
  5  -- end of sample_data mimicking real table
  6  SELECT dt,
  7    TO_CHAR(dt, 'DY') DAY,
  8    NVL(
  9    CASE
 10      WHEN dt < DATE '2016-01-01'
 11      THEN TO_CHAR(dt, 'IW')
 12      WHEN dt >= next_day(TRUNC(DATE '2016-01-01', 'YYYY') - 1, 'Monday')
 13      THEN TO_CHAR(dt                                      +7, 'IW')
 14    END, '01') week_number
 15  FROM sample_data;

DT         DAY WEEK_NUMBER
---------- --- -----------
2015-12-28 MON 53
2015-12-29 TUE 53
2015-12-30 WED 53
2015-12-31 THU 53
2016-01-01 FRI 01
2016-01-02 SAT 01
2016-01-03 SUN 01
2016-01-04 MON 02
2016-01-05 TUE 02
2016-01-06 WED 02
2016-01-07 THU 02
2016-01-08 FRI 02
2016-01-09 SAT 02
2016-01-10 SUN 02
2016-01-11 MON 03

15 rows selected.

NOTE:

The value 15 to generate 15 rows and the dates are hard-coded above just for demonstration using the WITH clause since OP did not provide the test case with create and insert statements. In reality, you need to use your table and column names.

edited Jun 20 '20 at 09:12

Community

1
1

answered Feb 17 '16 at 08:13

Lalit Kumar B

47,486
13
97
124

No, WW does not start a week from monday as required (monday reset)! 'WW' starts from first day in year regardless whether it is monday or any other day in week. – sbrbot Feb 17 '16 at 08:34
@sbrbot It is going to be a complex logic, wait a couple of minutes. Will update my answer. – Lalit Kumar B Feb 17 '16 at 09:05
Ok this will work for year 2016 while it is hard coded into SQL (in CASE statement). I can acchieve this for one year easily with TO_CHAR(date,'IW')+1, but that's not the point. I'd like to have universal code that will work for all previous and next years. – sbrbot Feb 17 '16 at 09:06
@sbrbot See the updated answer. The value `15` to generate 15 rows and the dates are hard-coded right now just for demonstration as you did not provide the create and insert statements. In your case you just need to use your table and column names. – Lalit Kumar B Feb 17 '16 at 09:16
I appreciate your effort in answering my question but sorry your answer is not correct. It fits only for year 2016. As mentioned before, you still have 2016 year hardcoded inside the code and that's why this code does not work correctly for other years having various offset of very first monday in year (try 2014), even more in this last code you added one more hardcoded thing - 'Monday' (this code does not work in my Oracle while this is NLS dependent - in my case it works with 'Ponedjeljak' which is 'Monday' in my language). I was looking for universal solution which will work with all years. – sbrbot Feb 18 '16 at 09:08
No problem. the hard-coded value is only for demonstration. – Lalit Kumar B Feb 18 '16 at 09:14

score 0 · Answer 2 · answered Feb 17 '16 at 10:43

An approach could be counting the number of days of the year and divide by 7, with some logic to handle the beginning and the end ot the week and of the year:

with test(date_) as 
(
select to_date('23122016', 'ddmmyyyy') + level -1 from dual connect by level < 30
)
SELECT date_,
       floor( to_number( to_char( 
                                    greatest( least(
                                                    trunc(date_, 'iw')+6 ,
                                                    add_months( trunc(date_, 'YEAR'),12) -1
                                                   ),
                                              trunc(date_, 'yyyy')),
                                    'ddd'
                                 )
                        ) /7 +1
             ) week
FROM test

The LEAST is used to avoid going to the next year, while the GREATEST is useful to avoid going to the previous one.

sbrbot · Accepted Answer · 2016-02-18T09:41:59.263

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

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'))

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;
/

`TO_CHAR(dt,'WW')+1` You are forcing Oracle to do an implicit data type conversion which should be avoided. — Lalit Kumar B, Feb 18 '16 at 01:52
Instead of TO_CHAR(dt,'WW')+1 one can use TO_CHAR(dt+7,'WW') - one week drift. — sbrbot, Feb 18 '16 at 08:29
Yes, that is what I did. I think my answer gives you what you want. — Lalit Kumar B, Feb 18 '16 at 08:30
I'm withdrawing my prev comment, TO_CHAR(dt,'WW')+1 and TO_CHAR(dt+7,'WW') DO NOT give the same result on dates at the end of year!!! — sbrbot, Feb 18 '16 at 09:02

SQL working week in Oracle

3 Answers3

Linked