Home » SQL & PL/SQL » SQL & PL/SQL » no of weeks
no of weeks [message #261952] Fri, 24 August 2007 02:50 Go to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Hello All,

Can anyone tell me how to find the week number along with the date in that week of a particualr year.

eg :
WEEK DATE
_____________

01 01-jan-07

02 08-jan-07

03 21-jan-07

Waiting for the reply sir.

Regards,

Raj
Re: no of weeks [message #261954 is a reply to message #261952] Fri, 24 August 2007 02:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select sysdate, to_char(sysdate,'WW') ww, to_char(sysdate,'IW') iw from dual;
SYSDATE             WW IW
------------------- -- --
24/08/2007 09:51:51 34 34

1 row selected.

Regards
Michel
Re: no of weeks [message #262096 is a reply to message #261954] Fri, 24 August 2007 08:34 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Sir,

I got that output, but as I've mentioned I want the week numbers along with their dates of the year specified .

Like ex: being

WEEK NUMBER DATE
----------------------------------

01 01-jan-07
02 08-jan-07
03 15-jan-07
04 22-jan-07
05 29-jan-07
06 05-feb-07
.
.
.
52 25-dec-07


Sir, hope my doubt is clear now.so can you help me now.

Regards,

Raj
Re: no of weeks [message #262098 is a reply to message #262096] Fri, 24 August 2007 08:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for calendar. There were 2 threads on this subject last week.

Regards
Michel
Re: no of weeks [message #262099 is a reply to message #261952] Fri, 24 August 2007 08:41 Go to previous messageGo to next message
transfer
Messages: 53
Registered: August 2007
Location: transfer
Member
Building on Michel's answer :
select the_date, 
to_char(the_date ,'WW') as week_number, 
to_char(the_date ,'IW') as ISO_week_number
from (select trunc(sysdate, 'year')+level-1 the_date from dual connect by level <= 366)
where trunc(the_date, 'year') = trunc(sysdate, 'year')
and to_char(the_date ,'D') = 1;
THE_DATE WE IS
-------- -- --
01/01/07 01 01
08/01/07 02 02
15/01/07 03 03
...
24/12/07 52 52
31/12/07 53 01
Please note that the "first day of the week" depends on the NLS_TERRITORY setting. Also, look at how the ISO standard says that 2007/12/31 is actually in "week 1" of 2008 !
Re: no of weeks [message #262100 is a reply to message #262099] Fri, 24 August 2007 08:52 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
thanks a lot sashton.seems to be very good solution.
Re: no of weeks [message #262104 is a reply to message #262100] Fri, 24 August 2007 09:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Actually you only need to generate 53 rows and not 366 if you add 7 each time (there are 7 days in a week).

Regards
Michel
Re: no of weeks [message #262106 is a reply to message #262104] Fri, 24 August 2007 09:23 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Yes sir, as I want only the no. of weeks , it must be 53 and not 366.

Thank you once again sir.
Re: no of weeks [message #262648 is a reply to message #262104] Tue, 28 August 2007 00:30 Go to previous messageGo to next message
transfer
Messages: 53
Registered: August 2007
Location: transfer
Member
Michel,

Add 7 to what exactly?

Regards, Stew
Re: no of weeks [message #262664 is a reply to message #262648] Tue, 28 August 2007 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In the subquery, instead of adding 1 at each level to get each date, add 7 at each level to get each week (or more precisely each date of week corresponding to the first day of the year).

Regards
Michel
Re: no of weeks [message #263175 is a reply to message #262664] Wed, 29 August 2007 04:55 Go to previous messageGo to next message
transfer
Messages: 53
Registered: August 2007
Location: transfer
Member
I believe the original request was for each week of the year plus the "date of that week", which I took to be the first day of that week. Your simplified solution starts from whatever day of the week January 1st happens to be.

I wanted to make sure because this year Jan. 1st happens to be Monday, so the requester might think he has what he wants then be surprised in 2008.

Regards, Stew
Re: no of weeks [message #263197 is a reply to message #263175] Wed, 29 August 2007 06:28 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Week is a fuzzy idea. This is why Oracle supports 2 of them with WW and IW format models.
Now OP chooses the definition he wants.

Regards
Michel
Previous Topic: skipping errors with insert select
Next Topic: Comparing columns from different tables (merged by LF)
Goto Forum:
  


Current Time: Sun Dec 11 02:30:31 CST 2016

Total time taken to generate the page: 0.11715 seconds