Home » SQL & PL/SQL » SQL & PL/SQL » Week problem. (oracle 10)
Week problem. [message #445105] Thu, 25 February 2010 22:36 Go to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear All,

When i run following query
select dd, to_char(DD,'W') WEEK_NUMBER FROM ( 
select to_Date('01-mar-2009','dd-mon-yyyy')+rownum-1 dd from dual
connect by level<=(to_date('28-feb-2010','dd-mon-yyyy')-to_date('01-mar-2009','dd-mon-yyyy')))


it return date with week number like.
DD	WEEK_NUMBER

3/1/2009	1
3/2/2009	1
3/3/2009	1
3/4/2009	1
3/5/2009	1
3/6/2009	1
3/7/2009	1
3/8/2009	2
3/9/2009	2
3/10/2009	2
3/11/2009	2
3/12/2009	2
3/13/2009	2
3/14/2009	2
3/15/2009	3
3/16/2009	3
3/17/2009	3
3/18/2009	3
3/19/2009	3
3/20/2009	3
3/21/2009	3
3/22/2009	4
3/23/2009	4
3/24/2009	4
3/25/2009	4
3/26/2009	4
3/27/2009	4
3/28/2009	4
3/29/2009	5
3/30/2009	5
3/31/2009	5
4/1/2009	1
4/2/2009	1
4/3/2009	1
4/4/2009	1
4/5/2009	1
4/6/2009	1


in above week number 5 is from 29-mar-2009 to 31-mar-2009 but i need that week 5 must complete its 7 days till date 04-Apr-2009. I mean week number 5 should display from 29-Mar-2009 to 04-Apr-2009 and week number 6 should from from 05-Apr-2009 to 11-Apr-2009 and so on.

Thanks in advance

Asif.

Re: Week problem. [message #445107 is a reply to message #445105] Thu, 25 February 2010 22:55 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
SELECT Trunc((dte - To_date('2009-03-01','YYYY-MM-DD')) / 7) + 1 week, dte
FROM   (SELECT To_date('2009-03-01','YYYY-MM-DD') + LEVEL - 1 dte
        FROM   dual
        CONNECT BY LEVEL <= To_date('2009-04-11','YYYY-MM-DD') + 1 - To_date('2009-03-01','YYYY-MM-DD')) 


Does this work for you?

[Updated on: Thu, 25 February 2010 23:09]

Report message to a moderator

Re: Week problem. [message #445109 is a reply to message #445105] Thu, 25 February 2010 22:59 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
select dd, trunc(lvl/7)+1 WEEK_NUMBER FROM ( 
select to_Date('01-mar-2009','dd-mon-yyyy')+rownum-1 dd, level-1 lvl from dual
connect by level<=(to_date('28-feb-2010','dd-mon-yyyy')-to_date('01-mar-2009','dd-mon-yyyy')))


regards,
Delna
Re: Week problem. [message #445130 is a reply to message #445105] Fri, 26 February 2010 02:08 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Thanks a lot. now i want to week number should be start by 1 from date 3/29/2009 to 04-apr-2009 and week number 2 should start from 05-apr-2009 and so on.

Regards.
asif.
Re: Week problem. [message #445131 is a reply to message #445130] Fri, 26 February 2010 02:12 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
delna already provided the solution ...
why dont you modify that as per your requirement?

sriram Smile
Previous Topic: Selecting only part of a string
Next Topic: Analytic Functions
Goto Forum:
  


Current Time: Wed Dec 07 23:55:28 CST 2016

Total time taken to generate the page: 0.11937 seconds