[sql] How to get first and last day of week in Oracle?

I need to get the first day and the last day of the week from some strings that have the format like this:

'201118'

where 2011 is the year and 18 is the number of the week. Knowing the number of the week, how do I get the first and the last day of the week?

How do I do this?

SQL> var P_YEARWEEK varchar2(6)
SQL> exec :P_YEARWEEK := '201118'

PL/SQL procedure successfully completed.

SQL> with t as
  2  ( select substr(:P_YEARWEEK,1,4) year
  3         , substr(:P_YEARWEEK,5,2) week
  4      from dual
  5  )
  6  select year
  7       , week
  8       , trunc(to_date(year,'yyyy'),'yyyy')                              january_1
  9       , trunc(trunc(to_date(year,'yyyy'),'yyyy'),'iw')                  monday_week_1
 10       , trunc(trunc(to_date(year,'yyyy'),'yyyy'),'iw') + (week - 1) * 7 start_of_the_week
 11       , trunc(trunc(to_date(year,'yyyy'),'yyyy'),'iw') + week  * 7 - 1  end_of_the_week
 12    from t
 13  /

YEAR WE JANUARY_1           MONDAY_WEEK_1       START_OF_THE_WEEK   END_OF_THE_WEEK
---- -- ------------------- ------------------- ------------------- -------------------
2011 18 01-01-2011 00:00:00 27-12-2010 00:00:00 25-04-2011 00:00:00 01-05-2011 00:00:00

1 row selected.

Regards,
Rob.

SELECT NEXT_DAY (TO_DATE ('01/01/'||SUBSTR('201118',1,4),'MM/DD/YYYY')+(TO_NUMBER(SUBSTR('201118',5,2))*7)-3,'SUNDAY')-7 first_day_wk,
    NEXT_DAY (TO_DATE ('01/01/'||SUBSTR('201118',1,4),'MM/DD/YYYY')+(TO_NUMBER(SUBSTR('201118',5,2))*7)-3,'SATURDAY') last_day_wk FROM dual

Assuming you mean weeks relative to the first day of the year ...

SELECT first_day_of_week, first_day_of_week+6 last_day_of_week
FROM (
  SELECT TO_DATE(YEAR||'0101','YYYYMMDD') + 7 * (week-1) first_day_of_week
  FROM (
    SELECT substr(yearweek,1,4) YEAR, to_number(substr(yearweek,5)) week
    FROM (
      SELECT '201118' yearweek FROM dual
    )
  )
)
;

Another solution is

SELECT 
  ROUND((TRUNC(SYSDATE) - TRUNC(SYSDATE, 'YEAR')) / 7,0) CANTWEEK,
  NEXT_DAY(SYSDATE, 'SUNDAY') - 7 FIRSTDAY,
  NEXT_DAY(SYSDATE, 'SUNDAY') - 1 LASTDAY
FROM DUAL

You must concat the cantweek with the year

Yet another solution (Monday is the first day):

select 
    to_char(sysdate - to_char(sysdate, 'd') + 2, 'yyyymmdd') first_day_of_week
    , to_char(sysdate - to_char(sysdate, 'd') + 8, 'yyyymmdd') last_day_of_week
from
    dual

First day of week (Monday):

SELECT TO_DATE(to_char(sysdate,'YYYY')||'0101','YYYYMMDD') + 7 * to_number(to_char(sysdate,'WW')-1)-1 first_day_week FROM dual;

Last day of week (Sunday):

SELECT TO_DATE(to_char(sysdate,'YYYY')||'0101','YYYYMMDD') + 7 * to_number(to_char(sysdate,'WW')-1)+5 last_day_week FROM dual;

Substituting your date or date field in these formulas will work for you!

Unless this is a one-off data conversion, chances are you will benefit from using a calendar table.

Having such a table makes it really easy to filter or aggregate data for non-standard periods in addition to regular ISO weeks. Weeks usually behave a bit differently across companies and the departments within them. As soon as you leave "ISO-land" the built-in date functions can't help you.

create table calender(
   day           date      not null -- Truncated date
  ,iso_year_week number(6) not null -- ISO Year week  (IYYYIW)
  ,retail_week   number(6) not null -- Monday to Sunday (YYYYWW)
  ,purchase_week number(6) not null -- Sunday to Saturday (YYYYWW)
  ,primary key(day)
);

You can either create additional tables for "purchase_weeks" or "retail_weeks", or simply aggregate on the fly:

select a.colA
      ,a.colB
      ,b.first_day
      ,b.last_day
  from your_table_with_weeks a
  join (select iso_year_week
              ,min(day) as first_day
              ,max(day) as last_day
          from calendar
         group  
            by iso_year_week
       ) b on(a.iso_year_week = b.iso_year_week)

If you process a large number of records, aggregating on the fly won't make a noticable difference, but if you are performing single-row you would benefit from creating tables for the weeks as well.

Using calendar tables provides a subtle performance benefit in that the optimizer can provide better estimates on static columns than on nested add_months(to_date(to_char())) function calls.

@cem's answer, has a flaw, if sysdate is a sunday, it returns the monday following. Inspired by his answer, here is one tested against few weeks:

select 
    (sysdate - to_char(sysdate-1, 'd') + 1) first_day_of_week --A monday here
from dual

WEEK

select TRUNC(sysdate, 'iw') AS iso_week_start_date,
       TRUNC(sysdate, 'iw') + 7 - 1/86400 AS iso_week_end_date
from dual;

MONTH

select 
TRUNC (sysdate, 'mm') AS month_start_date,
LAST_DAY (TRUNC (sysdate, 'mm')) + 1 - 1/86400 AS month_end_date
from dual;

I think this is just a simple select statement. I hope it works, because I couldn't test it at home, because I don't have a Oracle database here ;-)

select to_date('201118', 'YYYYWW'), to_date('201118', 'YYYYWW')+7 from dual;

You have to be carefully because there is a difference between WW and IW. Here is an article which explains the difference: http://rwijk.blogspot.com/2008/01/date-format-element-ww.html

Actually, I did something like this:

    select case myconfigtable.valpar
       when 'WEEK' then to_char(next_day(datetime-7,'Monday'),'DD/MM/YYYY')|| ' - '|| to_char(next_day(datetime,'Sunday'),'DD/MM/YYYY')
       when 'MONTH' then to_char(to_date(yearweek,'yyyyMM'),'DD/MM/YYYY')  || ' - '|| to_char(last_day(to_date(yearweek,'yyyyMM')),'DD/MM/YYYY')
       else 'NA'
       end
from
(
select to_date(YEAR||'01','YYYYMM') + 7 * (WEEK - 1) datetime, yearweek
       from
       (
       select substr(yearweek,1,4) YEAR,
              to_number(substr(yearweek,5)) WEEK,
              yearweek
       from (select '201018' yearweek from dual
            )
       )
), myconfigtable myconfigtable
 where myconfigtable.codpar='TYPEOFPERIOD'

You could try this approach:

1. Get the current date.

2. Add the the number of years which is equal to the difference between the current date's year and the specified year.

3. Subtract the number of days that is the last obtained date's week day's number (and it will give us the last day of some week).

4. Add the number of weeks which is the difference between the last obtained date's week number and the specified week number, and that will yield the last day of the desired week.

5. Subtract 6 days to obtain the first day.

SELECT TRUNC(SYSDATE,'D') "1ST_DAY", TRUNC(SYSDATE,'D') + 6 LAST_DAY FROM DUAL

If you are using Oracle, this code can help you:

select 
TRUNC(sysdate, 'YEAR') Start_of_the_year,
TRUNC(sysdate, 'MONTH') Start_of_the_month,
TRUNC(sysdate, 'DAY') start_of_the_week,
TRUNC(sysdate+365, 'YEAR')-1 End_of_the_year,
TRUNC(sysdate+30, 'MONTH')-1 End_of_the_month,
TRUNC(sysdate+6, 'DAY')-1 end_of_the_week
from dual;


select 
TRUNC(sysdate, 'YEAR') Start_of_the_year,
TRUNC(sysdate+365, 'YEAR')-1 End_of_the_year,
TRUNC(sysdate, 'MONTH') Start_of_the_month,
TRUNC(sysdate+30, 'MONTH')-1 End_of_the_month,
TRUNC(sysdate, 'DAY')+1 start_of_the_week,  -- starting Monday
TRUNC(sysdate+6, 'DAY') end_of_the_week     -- finish Sunday
from dual;