Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

using to_char('date', IW) in a different way

user13328581Jan 10 2011 — edited Jan 10 2011
dear all;

I have the following sample data shown below
create table table_one
(
 v_id varchar2(60),
 close_date date
);

insert into table_one
  (v_id, close_date)
values
  ('A', to_date('1/1/2010 4:47:09 PM', 'MM/DD/YYYY HH:MI:SS:AM'));  
insert into table_one
  (v_id, close_date)
values
  ('A', to_date('1/2/2010 2:47:09 PM', 'MM/DD/YYYY HH:MI:SS:AM'));  
insert into table_one
  (v_id, close_date)
values
  ('A', to_date('1/3/2010 1:47:19 PM', 'MM/DD/YYYY HH:MI:SS:AM'));   
insert into table_one
  (v_id, close_date)
values
  ('D', to_date('1/4/2010 5:47:09 PM', 'MM/DD/YYYY HH:MI:SS:AM'));  
insert into table_one
  (v_id, close_date)
values
  ('D', to_date('1/5/2010 4:47:09 PM', 'MM/DD/YYYY HH:MI:SS:AM'));  
insert into table_one
  (v_id, close_date)
values
  ('D', to_date('1/6/2010 5:17:09 PM', 'MM/DD/YYYY HH:MI:SS:AM'));  


insert into table_one
  (v_id, close_date)
values
  ('D', to_date('1/7/2010 5:17:09 PM', 'MM/DD/YYYY HH:MI:SS:AM'));  
  insert into table_one
  (v_id, close_date)
values
  ('D', to_date('1/8/2010 5:17:09 PM', 'MM/DD/YYYY HH:MI:SS:AM'));  
  insert into table_one
  (v_id, close_date)
values
  ('D', to_date('1/9/2010 5:17:09 PM', 'MM/DD/YYYY HH:MI:SS:AM'));  
  insert into table_one
  (v_id, close_date)
values
  ('D', to_date('1/10/2010 5:17:09 PM', 'MM/DD/YYYY HH:MI:SS:AM'));  
  insert into table_one
  (v_id, close_date)
values
  ('L', to_date('1/11/2010 5:17:09 PM', 'MM/DD/YYYY HH:MI:SS:AM'));  
  insert into table_one
  (v_id, close_date)
values
  ('L', to_date('1/12/2010 5:17:09 PM', 'MM/DD/YYYY HH:MI:SS:AM'));  
  insert into table_one
  (v_id, close_date)
values
  ('L', to_date('1/13/2010 5:17:09 PM', 'MM/DD/YYYY HH:MI:SS:AM'));  
    insert into table_one
  (v_id, close_date)
values
  ('L', to_date('1/14/2010 5:17:09 PM', 'MM/DD/YYYY HH:MI:SS:AM'));  
and basically I have the following sql that needs to be modified
  select y.v_id, to_char(y.close_date, 'IW') as d_date   from table_one y
  group by to_char(y.close_date, 'IW'), y.v_id;
this query gives me this output below

V_ID     D_DATE
D              01
L               02
A               53
However though, this is what I want instead
V_ID             D_DATE
D                 1/4/2010  - 1/10/2010
L                  1/11/2010 - 1/17/2010
A                 1/1/2010 - 1/3/2010
This is all based on the ISO Week standard. Thank you.
All help is appreciated
This post has been answered by Frank Kulash on Jan 10 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 7 2011
Added on Jan 10 2011
5 comments
724 views