A long while back someone asked the following question, which seems simple enough:
Is there a quick way to convert week number (of the year) to start date of that week?
EX: If I pass week number as 1 then it should return 1/1/2009
If I pass week number as 7 then it should return 2/8/2009
That same question popped up just recently so I dug out the example I made in2009 which is still relevant today. The concept assumes a number of things which may or may not be true, such as the first day of week 1 is January 1 and that week 7 is defined to include, and also begin on, February 8. Such assumptions depend upon which week numbering ‘scheme’ one elects to use, and there are two common schemes currently in use by Oracle. Let’s look at both of them and see what differences they contain and how they can throw the listed assumptions ‘out of the window’.
The two week numbering systems in use by Oracle are the U.S. week numbering system and the ISO week numbering system. They ARE different in how they define Week number 1 and that can throw a ‘monkey wrench’ into any methodology one could implement to answer the above listed question.
If we use the U.S week numbering system we can easily satisfy the first condition listed in the posted question as Week 2 is defined as the week containing January 1:
SQL> with date_wk as (
2 select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/RRRR') + rownum - 1 dt
3 from dual
4 connect by level <= 366
5 ),
6 wk_dt as (
7 select dt, to_number(to_char(dt, 'ww')) wk_of_yr
8 from date_wk
9 )
10 select dt, wk_of_yr
11 from wk_dt
12 where wk_of_yr = &&1
13 /
DT WK_OF_YR
--------- ----------
01-JAN-09 1
02-JAN-09 1
03-JAN-09 1
04-JAN-09 1
05-JAN-09 1
06-JAN-09 1
07-JAN-09 1
01-JAN-10 1
8 rows selected.
SQL>
SQL> with date_wk as (
2 select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/RRRR') + rownum - 1 dt
3 from dual
4 connect by level <= 366
5 ),
6 wk_dt as (
7 select dt, to_number(to_char(dt, 'ww')) wk_of_yr
8 from date_wk
9 )
10 select min(dt)
11 from wk_dt
12 where wk_of_yr = &&1
13 /
MIN(DT)
---------
01-JAN-09
SQL>
[In Oracle syntax the format specifier for the U.S. week numbering system is WW, in either upper or lower case. The first subquery shown generates a list of dates starting with January 1 of the current year and ends 365 days later. The second subquery takes that list and generates the U.S. week number for each date. The final query returns results based upon the supplied week number.]
But Week 7 of that numbering convention doesn’t contain February 8, 2009:
SQL> with date_wk as (
2 select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/RRRR') + rownum - 1 dt
3 from dual
4 connect by level <= 366
5 ),
6 wk_dt as (
7 select dt, to_number(to_char(dt, 'ww')) wk_of_yr
8 from date_wk
9 )
10 select dt, wk_of_yr
11 from wk_dt
12 where wk_of_yr = &&1
13 /
DT WK_OF_YR
--------- ----------
12-FEB-09 7
13-FEB-09 7
14-FEB-09 7
15-FEB-09 7
16-FEB-09 7
17-FEB-09 7
18-FEB-09 7
7 rows selected.
SQL>
SQL> with date_wk as (
2 select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/RRRR') + rownum - 1 dt
3 from dual
4 connect by level <= 366
5 ),
6 wk_dt as (
7 select dt, to_number(to_char(dt, 'ww')) wk_of_yr
8 from date_wk
9 )
10 select min(dt)
11 from wk_dt
12 where wk_of_yr = &&1
13 /
MIN(DT)
---------
12-FEB-09
SQL>
Week 6 does, although it’s not the starting date of that week:
SQL> with date_wk as (
2 select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/RRRR') + rownum - 1 dt
3 from dual
4 connect by level <= 366
5 ),
6 wk_dt as (
7 select dt, to_number(to_char(dt, 'ww')) wk_of_yr
8 from date_wk
9 )
10 select dt, wk_of_yr
11 from wk_dt
12 where wk_of_yr = &&1
13 /
DT WK_OF_YR
--------- ----------
05-FEB-09 6
06-FEB-09 6
07-FEB-09 6
08-FEB-09 6
09-FEB-09 6
10-FEB-09 6
11-FEB-09 6
7 rows selected.
SQL>
SQL> with date_wk as (
2 select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/RRRR') + rownum - 1 dt
3 from dual
4 connect by level <= 366
5 ),
6 wk_dt as (
7 select dt, to_number(to_char(dt, 'ww')) wk_of_yr
8 from date_wk
9 )
10 select min(dt)
11 from wk_dt
12 where wk_of_yr = &&1
13 /
MIN(DT)
---------
05-FEB-09
SQL>
Now, if the ISO week numbering convention is used the first condition of the question won’t be satisfied as Week 1 is defined to contain the first Thursday of the calendar year, thus the starting date for ISO Week 1 can be in December, and for 2009 it is:
SQL> with date_wk as (
2 select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/RRRR') + rownum - 4 dt
3 from dual
4 connect by level <= 366
5 ),
6 wk_dt as (
7 select dt, to_number(to_char(dt, 'iw')) wk_of_yr
8 from date_wk
9 )
10 select dt, wk_of_yr
11 from wk_dt
12 where wk_of_yr = &&1
13 /
DT WK_OF_YR
--------- ----------
29-DEC-08 1
30-DEC-08 1
31-DEC-08 1
01-JAN-09 1
02-JAN-09 1
03-JAN-09 1
04-JAN-09 1
7 rows selected.
SQL>
SQL> with date_wk as (
2 select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/RRRR') + rownum - 4 dt
3 from dual
4 connect by level <= 366
5 ),
6 wk_dt as (
7 select dt, to_number(to_char(dt, 'iw')) wk_of_yr
8 from date_wk
9 )
10 select min(dt)
11 from wk_dt
12 where wk_of_yr = &&1
13 /
MIN(DT)
---------
29-DEC-08
SQL>
[The Oracle format specifier for the ISO week numbering system is IW, in either upper or lower case. The change to the format specifier is the only change made to the query posted at the beginning.]
ISO Week 7 doesn’t answer the second condition, either, since February 8, 2009 is the last day of ISO Week 6:
SQL> with date_wk as (
2 select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/RRRR') + rownum - 4 dt
3 from dual
4 connect by level <= 366
5 ),
6 wk_dt as (
7 select dt, to_number(to_char(dt, 'iw')) wk_of_yr
8 from date_wk
9 )
10 select dt, wk_of_yr
11 from wk_dt
12 where wk_of_yr = &&1
13 /
DT WK_OF_YR
--------- ----------
09-FEB-09 7
10-FEB-09 7
11-FEB-09 7
12-FEB-09 7
13-FEB-09 7
14-FEB-09 7
15-FEB-09 7
7 rows selected.
SQL>
SQL> with date_wk as (
2 select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/RRRR') + rownum - 4 dt
3 from dual
4 connect by level <= 366
5 ),
6 wk_dt as (
7 select dt, to_number(to_char(dt, 'iw')) wk_of_yr
8 from date_wk
9 )
10 select min(dt)
11 from wk_dt
12 where wk_of_yr = &&1
13 /
MIN(DT)
---------
09-FEB-09
SQL>
How, then, is the ISO week defined? It starts on Monday and ends on Sunday, and ISO Week 1 is defined in the following equivalent terms:
The week with the year’s first Thursday in it (the ISO 8601 definition) the week starting with the Monday which is nearest in time to 1 January the week with the year’s first working day in it (if Saturdays, Sundays, and 1 January are not working days) the week with January 4 in it the first week with the majority (four or more) of its days in the starting year the week starting with the Monday in the period 29 December – 4 January the week with the Thursday in the period 1 – 7 January If 1 January is on a Monday, Tuesday, Wednesday or Thursday, it is in week 01. If 1 January is on a Friday, Saturday or Sunday, it is in week 52 or 53 of the previous year.
Given the above definition there are some years where even the first condition of the original question won’t be satisfied, like 2010, where the first day of ISO Week 1 is January 4:
SQL> with date_wk as (
2 select to_date('01/01/2010', 'MM/DD/RRRR') + rownum - 1 dt
3 from dual
4 connect by level <= 366
5 ),
6 wk_dt as (
7 select dt, to_number(to_char(dt, 'iw')) wk_of_yr
8 from date_wk
9 )
10 select dt, wk_of_yr
11 from wk_dt
12 where wk_of_yr = &&1
13 /
DT WK_OF_YR
--------- ----------
04-JAN-10 1
05-JAN-10 1
06-JAN-10 1
07-JAN-10 1
08-JAN-10 1
09-JAN-10 1
10-JAN-10 1
7 rows selected.
SQL>
SQL> with date_wk as (
2 select to_date('01/01/2010', 'MM/DD/RRRR') + rownum - 1 dt
3 from dual
4 connect by level <= 366
5 ),
6 wk_dt as (
7 select dt, to_number(to_char(dt, 'iw')) wk_of_yr
8 from date_wk
9 )
10 select min(dt)
11 from wk_dt
12 where wk_of_yr = &&1
13 /
MIN(DT)
---------
04-JAN-10
SQL>
The U.S. week is defined as starting on Sunday and ending on Saturday. Week number 1 in this convention is defined as the week beginning on January 1, which may be a partial week based on the convention that calendar weeks start on Sunday and end on Saturday. As such the last week of the year in this convention can also be a partial week based on the convention stated in the previous sentence. The full definition of U.S. Week Number 1 is:
The first week of the year contains 1 January, the 1st Saturday and is comprised of days 1-7 of the year.
This allows the first week of the year to start on any day of the conventional week and end six days later; the first week could run from Wednesday to Tuesday rather than from Sunday to Saturday. So, the question, as posed, relies upon a numbering system which allows partial weeks, the weeks always start on Sunday (so how does a partial week occur?), always end on Saturday and declare Week Number 1 as that week starting with January 1 (a criteria that can run afoul of the Sunday to Saturday, 7 days in a week ‘rule’). In such a system February 8, 2009, would be the starting date for Week 7 (because Week 1 only has three days, January 1,2 and 3, a strange occurrence indeed as it contradicts the stated definition of every week starting on a Sunday). I don’t know of a numbering scheme which meets that conflicting criteria. But, there MIGHT be one in use somewhere which satisfies all of those conditions. Stranger things have happened.
And that was the week that wasn’t.
