Oracle: Getting The Starting Date Of A Week Using Week Number

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.

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles