Home » SQL & PL/SQL » SQL & PL/SQL » Organizing series of Dates into two different columns (Oracle 11g)
Organizing series of Dates into two different columns [message #318898] Thu, 08 May 2008 07:17 Go to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi All

I have a column with dates ranging from
1 month to another month.

Example as follows


DATE_VAL
--------
25-OCT-07
26-OCT-07
27-OCT-07
28-OCT-07
29-OCT-07
30-OCT-07
31-OCT-07
01-NOV-07
02-NOV-07
03-NOV-07


Now i want to display in the following forma


START_DATE   END_DATE
----------   --------
25-OCT-07    31-OCT-07
01-NOV-07    03-NOV-07


Dates may range across multiple months.
In that case for each month we will have
different final record.

Please guide/help me in the regard.

Thanks
Natesh
Re: Organizing series of Dates into two different columns [message #318906 is a reply to message #318898] Thu, 08 May 2008 07:30 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You'll need to use MIN and MAX function, as well as GROUP BY clause.
Re: Organizing series of Dates into two different columns [message #318911 is a reply to message #318906] Thu, 08 May 2008 07:38 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Thanks A Lot. It is working. Cool
Re: Organizing series of Dates into two different columns [message #318912 is a reply to message #318911] Thu, 08 May 2008 07:40 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Would you mind to share the solution so that other Forum members might benefit from it?
Re: Organizing series of Dates into two different columns [message #318937 is a reply to message #318912] Thu, 08 May 2008 08:33 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Obviously Mr LittleFoot. My Pleasure.


SQL> select min(date_val), max(date_val)
from temp1
group by to_char(date_val,'mon'), to_char(date_val,'yy')
order by 1;



--Nat
Re: Organizing series of Dates into two different columns [message #318939 is a reply to message #318937] Thu, 08 May 2008 08:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64118
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"trunc(<a date>, 'month')" truncates the date to the first day of month.
It is better than "to_char(date_val,'mon'), to_char(date_val,'yy')"

Regards
Michel

[Updated on: Thu, 08 May 2008 08:49]

Report message to a moderator

Re: Organizing series of Dates into two different columns [message #318944 is a reply to message #318939] Thu, 08 May 2008 08:40 Go to previous message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Thanks for the suggestion Mic
Previous Topic: Right syntax
Next Topic: an odd problem about unique key when inserting into table
Goto Forum:
  


Current Time: Tue Dec 06 04:57:43 CST 2016

Total time taken to generate the page: 0.18064 seconds