Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    1

    Unanswered: Extract Date from week no and year provided

    I need to check dates in given week for given year. I have week no and year from this I have to ignore all dates which comes under specified week. Help me.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Here is a function that will return the first date in the given week (assuming weeks start on a Monday - if not, change the code):
    Code:
    SQL> create or replace function week_start(p_year in integer, p_week in integer)
      2  return date
      3  is
      4  begin
      5    return next_day(to_date(p_year||'0101','YYYYMMDD')-1,'MON')+(p_week-1)*7;
      6  end;
      7  /
    
    Function created.
    
    SQL> select week_start(2004,1) from dual;
    
    WEEK_START(
    -----------
    05-JAN-2004
    
    SQL> select week_start(2004,52) from dual;
    
    WEEK_START(
    -----------
    27-DEC-2004
    (You might want to add some validation about weeks being between 1 and 53, etc.)

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    create or replace function weeks(p_year in number, p_week in number)
    return date
    is
    wrk_date date;
    begin
    select (to_date(to_char(p_year)||'0101','YYYYMMDD') - to_number(to_char(to_date(to_char(p_year)||'0101', 'YYYYMMDD'),'D'))+2) + (p_week*7)
    into wrk_date
    from dual;

    return wrk_date
    end;

    This select assumes that the variables p_year is a number that equals a 4 digit year and that p_week is the week of the year to return.
    Last edited by beilstwh; 11-26-04 at 10:46.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •