| Change Calendar Day to Sunday [message #25672] |
Sat, 10 May 2003 11:19  |
sandeep
Messages: 110
Registered: October 2000
|
Senior Member |
|
|
I want to group all records by week.And week should start from Sunday and end on Saturday. to_char(column_name,'iw-yyyy') returns me records for week- MOnday to Sunday as per ISO standard. NLS_territory is already set to America.Calendar is Gregorian.
Pls suggest me either a query to get group by week(Sunday to saturday) or to change calendar day to Sunday.
|
|
|
|
|
|
| Re: Change Calendar Day to Sunday [message #25806 is a reply to message #25676] |
Mon, 19 May 2003 22:41  |
sandeep
Messages: 110
Registered: October 2000
|
Senior Member |
|
|
A brilliant reply. It works...except for a small problem. If I search for records that involves end of year 2002 and start of 2003, i.e for the week starting from Dec 29 2002 to Jan 4 2003, I get two records viz. 1-2002 and 1-2003 (ww-yyyy format ).
Can anyone1 suggest me an answer for this?
Thanks.
|
|
|
|
| Re: Change Calendar Day to Sunday [message #25898 is a reply to message #25676] |
Fri, 23 May 2003 12:47 |
Art Metzer
Messages: 2480
Registered: December 2002
|
Senior Member |
|
|
SQL> CREATE TABLE t (d DATE, n NUMBER);
Table created.
SQL> INSERT INTO t
2 SELECT TO_DATE('20021130'
3 , 'YYYYMMDD') + ROWNUM
4 , MOD(ABS(DBMS_RANDOM.RANDOM),20) + 1
5 FROM sys.all_users
6 WHERE ROWNUM <= 60
7 /
60 rows created.
SQL> COMMIT;
Commit complete.
SQL> SELECT TO_CHAR(d,'fmDy MM/DD/YYYY') dt
2 , n
3 FROM T
4 ORDER BY d
5 /
DT N
-------------- ----------
Sun 12/1/2002 1
Mon 12/2/2002 10
Tue 12/3/2002 16
Wed 12/4/2002 15
Thu 12/5/2002 15
Fri 12/6/2002 8
Sat 12/7/2002 11
Sun 12/8/2002 5
Mon 12/9/2002 13
Tue 12/10/2002 9
Wed 12/11/2002 11
Thu 12/12/2002 8
Fri 12/13/2002 11
Sat 12/14/2002 9
Sun 12/15/2002 11
Mon 12/16/2002 5
Tue 12/17/2002 17
Wed 12/18/2002 9
Thu 12/19/2002 9
Fri 12/20/2002 14
Sat 12/21/2002 5
Sun 12/22/2002 7
Mon 12/23/2002 6
Tue 12/24/2002 7
Wed 12/25/2002 8
Thu 12/26/2002 11
Fri 12/27/2002 4
Sat 12/28/2002 18
Sun 12/29/2002 6
Mon 12/30/2002 16
Tue 12/31/2002 12
Wed 1/1/2003 2
Thu 1/2/2003 7
Fri 1/3/2003 20
Sat 1/4/2003 4
Sun 1/5/2003 20
Mon 1/6/2003 19
Tue 1/7/2003 3
Wed 1/8/2003 10
Thu 1/9/2003 12
Fri 1/10/2003 19
Sat 1/11/2003 15
Sun 1/12/2003 14
Mon 1/13/2003 19
Tue 1/14/2003 5
Wed 1/15/2003 10
Thu 1/16/2003 14
Fri 1/17/2003 19
Sat 1/18/2003 3
Sun 1/19/2003 18
Mon 1/20/2003 15
Tue 1/21/2003 1
Wed 1/22/2003 14
Thu 1/23/2003 3
Fri 1/24/2003 10
Sat 1/25/2003 20
Sun 1/26/2003 10
Mon 1/27/2003 12
Tue 1/28/2003 18
Wed 1/29/2003 17
60 rows selected.
SQL> <font color=blue>SELECT TO_CHAR(TRUNC(d + 1,'IW') - 1,'YYYY-IW')</font>
2 <font color=blue>, SUM(n)</font>
3 <font color=blue>FROM t</font>
4 <font color=blue>GROUP BY TO_CHAR(TRUNC(d + 1,'IW') - 1,'YYYY-IW')</font>
5 <font color=blue>ORDER BY TO_CHAR(TRUNC(d + 1,'IW') - 1,'YYYY-IW')</font>
6 /
TO_CHAR SUM(N)
------- ----------
2002-48 76
2002-49 66
2002-50 70
2002-51 61
2002-52 67
2003-01 98
2003-02 84
2003-03 81
2003-04 57
9 rows selected.
SQL>
A
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
