Week problem. [message #445105] |
Thu, 25 February 2010 22:36  |
mamalik
Messages: 270 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   |
 |
BlackSwan
Messages: 26766 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
|
|
|
|
|
|