LinuxQuestions.org - oracle + working with date/week

- Programming (https://www.linuxquestions.org/questions/programming-9/)

- - oracle + working with date/week (https://www.linuxquestions.org/questions/programming-9/oracle-working-with-date-week-213729/)

champ

08-05-2004 07:09 AM

oracle + working with date/week

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

PL/SQL get a date from a week

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

date range - given weekno and year

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

Need add week function in oracle

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".

All times are GMT -5. The time now is 09:19 PM.