Home » SQL & PL/SQL » SQL & PL/SQL » return correct ww
return correct ww [message #355979] Tue, 28 October 2008 20:36 Go to next message
ejoeyz_85
Messages: 30
Registered: October 2008
Member
Hi all,

I have a problem for retrieving a correct ww. I hit a snag while testing the serverPerformanceWeekly script. It seems the TO_CHAR(<date>,'WW') function is not returning the correct value. See sample below for 26-Oct-2008 (last Sunday), 43 was returned instead of 44. How shud i do?

SQL> select to_char(to_date('20081026','YYYYMMDD'),'WW') from serverPerformanceWeekly;
 
TO
--
43

 SQL> select to_char(sysdate, 'WW') from dual;

44

my script for serverPerformanceWeekly.

select hostname, to_char(to_date(&date_start, 'YYYYMMDD') , 'YYYY'), 
to_char(to_date(&date_start, 'YYYYMMDD') , 'WW'), 
genesis_port, instance_name,
       api_name, sum(total_time), max(max_time), 
min(min_time), (sum(total_time)/sum(count)), sum(count)
  from gen_api_performance_daily
 where to_char(statistics_date,'YYYYMMDD') between &date_start and &date_end 
 group by hostname, to_char(to_date(&date_start, 'YYYYMMDD') , 'YYYY'), 
to_char(to_date(&date_start, 'YYYYMMDD') , 'WW'), 
genesis_port, instance_name, api_name;

[Updated on: Tue, 28 October 2008 23:53] by Moderator

Report message to a moderator

Re: return correct ww [message #355993 is a reply to message #355979] Tue, 28 October 2008 23:23 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
43 is correct as per the online documentation 'ww' is "Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year."
Re: return correct ww [message #355995 is a reply to message #355993] Tue, 28 October 2008 23:38 Go to previous messageGo to next message
ejoeyz_85
Messages: 30
Registered: October 2008
Member
can you explain more?
Re: return correct ww [message #355999 is a reply to message #355995] Tue, 28 October 2008 23:47 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/sql_elements004.htm#CDEHIFJA
Re: return correct ww [message #356003 is a reply to message #355999] Tue, 28 October 2008 23:54 Go to previous messageGo to next message
ejoeyz_85
Messages: 30
Registered: October 2008
Member
Quote:
Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.



could u give the example of the any scenario?
Re: return correct ww [message #356004 is a reply to message #355999] Tue, 28 October 2008 23:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Laughing

Exactly what I posted to his question on usenet groups.

Re: return correct ww [message #356005 is a reply to message #356003] Tue, 28 October 2008 23:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ejoeyz_85 wrote on Wed, 29 October 2008 05:54
Quote:
Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.



could u give the example of the any scenario?

Take 100 dates from the 10 previous years and you will have your examples.
Why do you want us to do it for you when you can easily do it yourself?

Regards
Michel

Re: return correct ww [message #356114 is a reply to message #356003] Wed, 29 October 2008 11:17 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
What I suspect that you are not understanding is that with 'WW' the first day of the week is not necessarily Monday or Sunday, it is whatever day January 1st of that year happens to fall on. So, since January 1, 2008 was a Tuesday the weeks returned by 'WW' for the year 2008 are Tuesday through Monday. So, week 43 is Tuesday Oct 21, 2008 through Monday October 27, 2008. So, October 26, 2008 is in week 43, not week 44. Week 44 begins on Tuesday October 28, 2008. However, if you want weeks that begin on Monday and end on Sunday, regardless of what day of the week January 1sts was, then you can use 'IW' instead of 'WW'. Please see the two demonstrations of 'WW' and 'IW' below.


-- 'WW' where each week begins on the same day of the week as the first day of the year (Tuesday in 2008):
SCOTT@orcl_11g> WITH	dates_2008 AS
  2  	     (SELECT  TO_DATE ('20080101', 'YYYYMMDD') + ROWNUM - 1 AS the_date
  3  	      FROM    DUAL
  4  	      CONNECT BY LEVEL <= 365)
  5  SELECT  TO_CHAR (the_date, 'WW') AS wk,
  6  	     TO_CHAR (MIN (the_date), 'DY DD-MON-YYYY') AS first_day,
  7  	     TO_CHAR (MAX (the_date), 'DY DD-MON-YYYY') AS last_day
  8  FROM    dates_2008
  9  GROUP   BY TO_CHAR (the_date, 'WW')
 10  ORDER   BY wk
 11  /

WK FIRST_DAY       LAST_DAY
-- --------------- ---------------
01 TUE 01-JAN-2008 MON 07-JAN-2008
02 TUE 08-JAN-2008 MON 14-JAN-2008
03 TUE 15-JAN-2008 MON 21-JAN-2008
04 TUE 22-JAN-2008 MON 28-JAN-2008
05 TUE 29-JAN-2008 MON 04-FEB-2008
06 TUE 05-FEB-2008 MON 11-FEB-2008
07 TUE 12-FEB-2008 MON 18-FEB-2008
08 TUE 19-FEB-2008 MON 25-FEB-2008
09 TUE 26-FEB-2008 MON 03-MAR-2008
10 TUE 04-MAR-2008 MON 10-MAR-2008
11 TUE 11-MAR-2008 MON 17-MAR-2008
12 TUE 18-MAR-2008 MON 24-MAR-2008
13 TUE 25-MAR-2008 MON 31-MAR-2008
14 TUE 01-APR-2008 MON 07-APR-2008
15 TUE 08-APR-2008 MON 14-APR-2008
16 TUE 15-APR-2008 MON 21-APR-2008
17 TUE 22-APR-2008 MON 28-APR-2008
18 TUE 29-APR-2008 MON 05-MAY-2008
19 TUE 06-MAY-2008 MON 12-MAY-2008
20 TUE 13-MAY-2008 MON 19-MAY-2008
21 TUE 20-MAY-2008 MON 26-MAY-2008
22 TUE 27-MAY-2008 MON 02-JUN-2008
23 TUE 03-JUN-2008 MON 09-JUN-2008
24 TUE 10-JUN-2008 MON 16-JUN-2008
25 TUE 17-JUN-2008 MON 23-JUN-2008
26 TUE 24-JUN-2008 MON 30-JUN-2008
27 TUE 01-JUL-2008 MON 07-JUL-2008
28 TUE 08-JUL-2008 MON 14-JUL-2008
29 TUE 15-JUL-2008 MON 21-JUL-2008
30 TUE 22-JUL-2008 MON 28-JUL-2008
31 TUE 29-JUL-2008 MON 04-AUG-2008
32 TUE 05-AUG-2008 MON 11-AUG-2008
33 TUE 12-AUG-2008 MON 18-AUG-2008
34 TUE 19-AUG-2008 MON 25-AUG-2008
35 TUE 26-AUG-2008 MON 01-SEP-2008
36 TUE 02-SEP-2008 MON 08-SEP-2008
37 TUE 09-SEP-2008 MON 15-SEP-2008
38 TUE 16-SEP-2008 MON 22-SEP-2008
39 TUE 23-SEP-2008 MON 29-SEP-2008
40 TUE 30-SEP-2008 MON 06-OCT-2008
41 TUE 07-OCT-2008 MON 13-OCT-2008
42 TUE 14-OCT-2008 MON 20-OCT-2008
43 TUE 21-OCT-2008 MON 27-OCT-2008
44 TUE 28-OCT-2008 MON 03-NOV-2008
45 TUE 04-NOV-2008 MON 10-NOV-2008
46 TUE 11-NOV-2008 MON 17-NOV-2008
47 TUE 18-NOV-2008 MON 24-NOV-2008
48 TUE 25-NOV-2008 MON 01-DEC-2008
49 TUE 02-DEC-2008 MON 08-DEC-2008
50 TUE 09-DEC-2008 MON 15-DEC-2008
51 TUE 16-DEC-2008 MON 22-DEC-2008
52 TUE 23-DEC-2008 MON 29-DEC-2008
53 TUE 30-DEC-2008 TUE 30-DEC-2008

53 rows selected.


-- 'IW' where each week begins on Monday and ends on Sunday:
SCOTT@orcl_11g> WITH	dates_2008 AS
  2  	     (SELECT  TO_DATE ('20071231', 'YYYYMMDD') + ROWNUM - 1 AS the_date
  3  	      FROM    DUAL
  4  	      CONNECT BY LEVEL <= 364)
  5  SELECT  TO_CHAR (the_date, 'IW') AS wk,
  6  	     TO_CHAR (MIN (the_date), 'DY DD-MON-YYYY') AS first_day,
  7  	     TO_CHAR (MAX (the_date), 'DY DD-MON-YYYY') AS last_day
  8  FROM    dates_2008
  9  GROUP   BY TO_CHAR (the_date, 'IW')
 10  ORDER   BY wk
 11  /

WK FIRST_DAY       LAST_DAY
-- --------------- ---------------
01 MON 31-DEC-2007 SUN 06-JAN-2008
02 MON 07-JAN-2008 SUN 13-JAN-2008
03 MON 14-JAN-2008 SUN 20-JAN-2008
04 MON 21-JAN-2008 SUN 27-JAN-2008
05 MON 28-JAN-2008 SUN 03-FEB-2008
06 MON 04-FEB-2008 SUN 10-FEB-2008
07 MON 11-FEB-2008 SUN 17-FEB-2008
08 MON 18-FEB-2008 SUN 24-FEB-2008
09 MON 25-FEB-2008 SUN 02-MAR-2008
10 MON 03-MAR-2008 SUN 09-MAR-2008
11 MON 10-MAR-2008 SUN 16-MAR-2008
12 MON 17-MAR-2008 SUN 23-MAR-2008
13 MON 24-MAR-2008 SUN 30-MAR-2008
14 MON 31-MAR-2008 SUN 06-APR-2008
15 MON 07-APR-2008 SUN 13-APR-2008
16 MON 14-APR-2008 SUN 20-APR-2008
17 MON 21-APR-2008 SUN 27-APR-2008
18 MON 28-APR-2008 SUN 04-MAY-2008
19 MON 05-MAY-2008 SUN 11-MAY-2008
20 MON 12-MAY-2008 SUN 18-MAY-2008
21 MON 19-MAY-2008 SUN 25-MAY-2008
22 MON 26-MAY-2008 SUN 01-JUN-2008
23 MON 02-JUN-2008 SUN 08-JUN-2008
24 MON 09-JUN-2008 SUN 15-JUN-2008
25 MON 16-JUN-2008 SUN 22-JUN-2008
26 MON 23-JUN-2008 SUN 29-JUN-2008
27 MON 30-JUN-2008 SUN 06-JUL-2008
28 MON 07-JUL-2008 SUN 13-JUL-2008
29 MON 14-JUL-2008 SUN 20-JUL-2008
30 MON 21-JUL-2008 SUN 27-JUL-2008
31 MON 28-JUL-2008 SUN 03-AUG-2008
32 MON 04-AUG-2008 SUN 10-AUG-2008
33 MON 11-AUG-2008 SUN 17-AUG-2008
34 MON 18-AUG-2008 SUN 24-AUG-2008
35 MON 25-AUG-2008 SUN 31-AUG-2008
36 MON 01-SEP-2008 SUN 07-SEP-2008
37 MON 08-SEP-2008 SUN 14-SEP-2008
38 MON 15-SEP-2008 SUN 21-SEP-2008
39 MON 22-SEP-2008 SUN 28-SEP-2008
40 MON 29-SEP-2008 SUN 05-OCT-2008
41 MON 06-OCT-2008 SUN 12-OCT-2008
42 MON 13-OCT-2008 SUN 19-OCT-2008
43 MON 20-OCT-2008 SUN 26-OCT-2008
44 MON 27-OCT-2008 SUN 02-NOV-2008
45 MON 03-NOV-2008 SUN 09-NOV-2008
46 MON 10-NOV-2008 SUN 16-NOV-2008
47 MON 17-NOV-2008 SUN 23-NOV-2008
48 MON 24-NOV-2008 SUN 30-NOV-2008
49 MON 01-DEC-2008 SUN 07-DEC-2008
50 MON 08-DEC-2008 SUN 14-DEC-2008
51 MON 15-DEC-2008 SUN 21-DEC-2008
52 MON 22-DEC-2008 SUN 28-DEC-2008

52 rows selected.

SCOTT@orcl_11g> 

Previous Topic: Group consecutive records
Next Topic: sum expression oddity (merged 2)
Goto Forum:
  


Current Time: Sat Dec 10 20:47:36 CST 2016

Total time taken to generate the page: 0.11422 seconds