Home » SQL & PL/SQL » SQL & PL/SQL » Group by months (Oracle9)
Group by months [message #320577] Thu, 15 May 2008 09:49 Go to next message
nigelcg
Messages: 4
Registered: April 2008
Junior Member
Can anybody think of a way to show the month and number of days between two dates.
IE starting 01/01/08 ending 05/03/08 if want to return
Jan 31
Feb 29
Mar 5

I have to use a select statment not PL/SQL as it will be in a business object.

Thanks
Re: Group by months [message #320585 is a reply to message #320577] Thu, 15 May 2008 10:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How to get the difference between 2 dates in format years/months/days

Regards
Michel
Re: Group by months [message #320604 is a reply to message #320577] Thu, 15 May 2008 11:06 Go to previous messageGo to next message
nigelcg
Messages: 4
Registered: April 2008
Junior Member
Thanks for that Michel, pretty useful but not exactly what I need. I'm trying to group by month rather than show the total number of months, like Jan 31 Feb 29 Mar 5.
I don't think it can be done but I hope I'm wrong.

Thanks
Re: Group by months [message #320607 is a reply to message #320604] Thu, 15 May 2008 11:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
trunc(<a date>,'MONTH') gives the first day of the month of <a date>.
last_day(<a date>) gives the last day of the month of <a date>.
to_char(<a date>, 'MM') gives the month number of a date.
Add this with a "row generator" (search for this) and you have your answer.

Regards
Michel
Re: Group by months [message #320629 is a reply to message #320577] Thu, 15 May 2008 12:59 Go to previous messageGo to next message
nigelcg
Messages: 4
Registered: April 2008
Junior Member

Michel,
Sorry but still can't see how this can be achieved using those functions.

Say in a table I have two date columns, start_date and end_date. Obviously I'll need a group by to break it down by month but can't come up with the syntax.

I want to select the month and number of days in that month between the start_date and end_date.

Thanks

Re: Group by months [message #320635 is a reply to message #320629] Thu, 15 May 2008 13:29 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I understand your question and the elements I gave allow you to solve it.
For "row generator" go to General forum and scroll down to "Puzzle n00 - Row generator" topic.
And also in the Wiki: Oracle Row Generator Techniques

Regards
Michel

[Updated on: Thu, 15 May 2008 13:31]

Report message to a moderator

Previous Topic: limit in forall colect
Next Topic: Database design document
Goto Forum:
  


Current Time: Fri Dec 09 17:33:11 CST 2016

Total time taken to generate the page: 0.17708 seconds