oracle + working with date/week

champ · 08-05-2004, 07:09 AM

Hi

Having som trouble with some SQL in oracle.
Is it possible to retreive the date range of a specified week? So if I input week 10 I would get the dates within that week. Or just the start date.

It's easy to do the other way around (getting the week from a date).

Code:

select to_char('10-AUG-2004', 'iw') from dual;

But this is more complicated.

Any idea guys?
Any help would be appriciated.

jim mcnamara · 08-05-2004, 03:19 PM

You're right we do something like this -

Code:

CREATE OR REPLACE  FUNCTION  week_start ( week_num IN NUMBER)
       return DATE
AS                 
value  	 	DATE :=NULL;
tmp_week  NUMBER :=0;
tmp_year  NUMBER :=0;

BEGIN 
    IF  week_num < 1 OR  week_num > 52
    then
          RETURN  value;  
    END  IF ;         
    SELECT  -- find current year
        TO_NUMBER(TO_CHAR(SYSDATE,'YYYY'))
    INTO  
        tmp_year
    FROM  
        dual;
    value:='01-JAN-' || TO_CHAR(tmp_year,'9999');    
    WHILE tmp_week != week_num  -- loop til we find week
    LOOP 
          SELECT  
                TO_NUMBER( TO_CHAR(value, 'IW') )
          INTO
                tmp_week
          FROM dual;
          IF tmp_week != week_num
          THEN
               value:=value + 1;
          END  IF ;          
    END LOOP;
    RETURN  value;
END week_start;
/                                   
-- example SQL call
select week_start(10) from dual;

champ · 08-06-2004, 01:52 AM

Thanks man.....exactly what I was looking for.

TBarman · 03-09-2010, 02:56 AM

You could also try:

create or replace
function getDateFromWeek(sYear_in in varchar2,sWeek_in in varchar2,iDay_in in number)return date
as
dRetDate date;
dCondate date;
nWeek pls_integer;
sWeek_one char(2);
begin

-- MONDAY: iDay_in==1
-- TUESDAY: iDay_in==2
-- WEDNESDAY: iDay_in==3
-- THURSDAY: iDay_in==4
-- FRIDAY: iDay_in==5
-- SATURDAY: iDay_in==6
-- SUNDAY: iDay_in==7

-- Find the first Monday
dCondate := to_date(sYear_in||'0101','YYYYMMDD');
sWeek_one := to_char(dCondate,'IW');
while (sWeek_one!='01') loop
dCondate := dCondate + 1;
sWeek_one := to_char(dCondate,'IW');
end loop;

-- Add the number of weeks
nWeek := to_number(sWeek_in);
nWeek := nWeek - 1;
dCondate := dCondate + (7*nWeek);

dRetDate := dCondate;
if(iDay_in>1 And iDay_in<8)then
dRetDate := dRetDate + iDay_in-1;
end if;

return dRetDate;
end;
/

dmadupu · 07-26-2011, 08:32 AM

Could you please tell me to get weeks date range ,given week no and year?

Thanks

dmadupu · 07-26-2011, 08:43 AM

I got it with the above code.Thanks

devnull10 · 07-26-2011, 05:14 PM

Not sure whether I understand your question but does this not give you what you want?

Code:

select TRUNC(TRUNC(SYSDATE,'YYYY')+(:WEEK_NUM*7),'IW') FROM DUAL;

Using say this week (week 30):

Quote:

select TRUNC(TRUNC(SYSDATE,'YYYY')+(30*7),'IW') FROM DUAL;

07/25/2011

I'm using a system with US date formats btw.

devnull10 · 07-26-2011, 06:07 PM

Doh - just seen the original post date of this lol.

shek · 11-28-2011, 05:45 PM

Hi there,
I need to add weeks in oracle. sysdate+(no_of_weeks*7) will not give correct result.

My requirement is like " need to get the same day after n weeks in oracle"
If today is MONDAY 28/11/2011 then after 6 weeks It should be MONDAY 09/01/2012...

select sysdate+(no_of_weeks*7) will create as issue when no of days in month is 31 or 28... please is there any function exist in oracle which will give " Same day date after n weeks".