Re: How to convert a year+week to a date ?

From: Deepest Blue <no_at_where.com>
Date: Tue, 6 Feb 2001 10:27:51 +0100
Message-ID: <981451696.73827@dedale.pandemonium.fr>

Finally I have written a function to perform this before having time to read your response :-(((

The biggest issue was to find the starting date of week 1, it must be the first *entire* week beggining with a monday.

Here is my code(better with a fixed font...):

drop package pk_util;
create or replace package pk_util as
  function week2date(param_week in varchar2) /* week is YYYYWW */

           return date;                      /* returns first day of week,
week 1 will start with 1st monday of year */
           pragma restrict_references(week2date,WNDS,WNPS,RNDS,RNPS);

end pk_util;
/

create or replace package body pk_util as   function week2date(param_week in varchar2)

           return date
  is

    year_str        varchar2(4); /* is year of parameter */
    day_number_0101 number;      /* of 1st january with 1=sunday to
7=saturday */
    week_number     number;      /* is week of parameter */
    week_start      date;        /* is the final date computed */

  begin
    year_str := substr(param_week,1,4);
    day_number_0101 := to_char(to_date('0101'||year_str,'DDMMYYYY'),'D');     week_number := substr(param_week,5,2);     week_start :=

to_date(to_char(((week_number-1)*7)+1,'009')||year_str,'DDDYYYY');


/* ------------------------------------ */

/* january --days--------------------   */

/* 1st     mo* tu  we  th  fr  sa  su   */

/* 2nd     tu  we  th  fr  sa  su  mo*  */

/* 3rd     we  th  fr  sa  su  mo* th   */

/* 4th     th  fr  sa  su  mo* tu  we   */

/* 5th     fr  sa  su  mo* tu  we  th   */

/* 6th     sa  su  mo* tu  we  th  fr   */

/* 7th     su  mo* tu  we  th  fr  sa   */

/* 8th     mo  tu  we  th  fr  sa  su   */

/* 9th     th  we  th  fr  sa  su  mo   */

/* 1st day  2   3   4   5   6   7   1   */

/* 1st mon 1 7 6 5 4 3 2 */
/* ------------------------------------ */

    if day_number_0101 = 1 then /* sunday */

      return week_start+1;
      end if;
    if day_number_0101 = 2 then /* monday */
      return week_start+0;
      end if;
    if day_number_0101 = 3 then /* tuesday */
      return week_start+6;
      end if;
    if day_number_0101 = 4 then /* wednesday */
      return week_start+5;
      end if;
    if day_number_0101 = 5 then /* thursday */
      return week_start+4;
      end if;
    if day_number_0101 = 6 then /* friday */
      return week_start+3;
      end if;
    if day_number_0101 = 7 then /* saturday */
      return week_start+2;
      end if;

  end week2date;
end pk_util;
/
Received on Tue Feb 06 2001 - 03:27:51 CST