LinuxQuestions.org
Register a domain and help support LQ
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 08-05-2004, 07:09 AM   #1
champ
Member
 
Registered: Jul 2002
Distribution: Slackware 10.0
Posts: 46

Rep: Reputation: 16
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.
 
Old 08-05-2004, 03:19 PM   #2
jim mcnamara
Member
 
Registered: May 2002
Posts: 964

Rep: Reputation: 34
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;
 
Old 08-06-2004, 01:52 AM   #3
champ
Member
 
Registered: Jul 2002
Distribution: Slackware 10.0
Posts: 46

Original Poster
Rep: Reputation: 16
Thanks man.....exactly what I was looking for.
 
Old 03-09-2010, 02:56 AM   #4
TBarman
LQ Newbie
 
Registered: Mar 2010
Posts: 1

Rep: Reputation: 0
Smile 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;
/
 
Old 07-26-2011, 08:32 AM   #5
dmadupu
LQ Newbie
 
Registered: Jul 2011
Posts: 2

Rep: Reputation: Disabled
date range - given weekno and year

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

Thanks
 
Old 07-26-2011, 08:43 AM   #6
dmadupu
LQ Newbie
 
Registered: Jul 2011
Posts: 2

Rep: Reputation: Disabled
I got it with the above code.Thanks
 
Old 07-26-2011, 05:14 PM   #7
devnull10
Member
 
Registered: Jan 2010
Location: Lancashire
Distribution: Slackware Stable
Posts: 547

Rep: Reputation: 115Reputation: 115
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.
 
Old 07-26-2011, 06:07 PM   #8
devnull10
Member
 
Registered: Jan 2010
Location: Lancashire
Distribution: Slackware Stable
Posts: 547

Rep: Reputation: 115Reputation: 115
Doh - just seen the original post date of this lol.
 
Old 11-28-2011, 05:45 PM   #9
shek
LQ Newbie
 
Registered: Nov 2011
Posts: 1

Rep: Reputation: Disabled
Lightbulb 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".
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
xhost + as user oracle not working, need to see oracle install GUI enzo250gto Linux - Software 2 02-11-2007 11:27 AM
Set up my first server last week. Now it is not working adrian29uk Linux - Networking 4 06-22-2005 03:44 AM
unable go set up a wlan with ndiswrapper, working for a week! dchirdon Linux - Wireless Networking 9 03-29-2005 07:44 PM
Mysql not working but it did last week dsiguy Linux - General 2 05-27-2003 01:59 PM
CD-ROM Stops Working After a Week XASCompuGuy Linux - Hardware 6 07-21-2002 12:01 AM


All times are GMT -5. The time now is 08:46 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration