Geeks With Blogs

News


Rodney Vinyard - .NET & SQL Developer When all is said and done, more will be said than done
Oracle - Select a list of months for a Date Range
 
Case 1 – the 13 months between '10/18/2007' & '11/17/2008'
 
select add_months (trunc (to_date('10/18/2007','MM/DD/YYYY'), 'MM'), 1*Level -1)
Month   FROM Dual
CONNECT BY Level <= MONTHS_BETWEEN(to_date('11/17/2008','MM/DD/YYYY'), to_date('10/18/2007','MM/DD/YYYY')) + 1
order by month
 
                 MONTH
----------------------
            1-Oct-2007
            1-Nov-2007
            1-Dec-2007
            1-Jan-2008
            1-Feb-2008
            1-Mar-2008
            1-Apr-2008
            1-May-2008
            1-Jun-2008
            1-Jul-2008
            1-Aug-2008
            1-Sep-2008
            1-Oct-2008
 
Case 2 – the 1 month shared between identical dates
 
select add_months (trunc (to_date('10/20/2007','MM/DD/YYYY'), 'MM'), 1*Level -1)
Month   FROM Dual
CONNECT BY Level <= MONTHS_BETWEEN(to_date('10/20/2007','MM/DD/YYYY'), to_date('10/20/2007','MM/DD/YYYY')) + 1
order by month
 
                 MONTH
----------------------
            1-Oct-2007
 
Posted on Friday, January 18, 2008 9:33 PM Oracle , Dates | Back to top


Comments on this post: Oracle - Select a list of months for a Date Range

# re: Oracle - Select a list of months for a Date Range
Requesting Gravatar...
gr8..Thank u vary much for this excellent query
Left by Philip on Nov 09, 2008 9:02 PM

# re: Oracle - Select a list of months for a Date Range
Requesting Gravatar...
Beautiful query. 10x.
Left by Argent on Mar 23, 2009 9:27 PM

# re: Oracle - Select a list of months for a Date Range
Requesting Gravatar...
excelent query
Left by Ted on Jun 10, 2009 2:11 PM

# re: Oracle - Select a list of months for a Date Range
Requesting Gravatar...
marvelleouds query
Left by Jayesh on Jun 22, 2009 4:24 AM

# re: Oracle - Select a list of months for a Date Range
Requesting Gravatar...
Thank u vary much
Left by Anil on Jul 08, 2009 11:36 AM

# re: Oracle - Select a list of Years up to curent year
Requesting Gravatar...
10x 4 the idea

Select TO_CHAR(v.year,'YYYY') as year
from (select add_months (to_date('01/01/1980','DD/MM/YYYY'), 12*Level-1)
Year FROM Dual
CONNECT BY Level <= trunc(MONTHS_BETWEEN(trunc (sysdate, 'YEAR'), to_date('01/01/1980','MM/DD/YYYY'))/12) + 1
) v
order by year desc
Left by avi on Jul 22, 2009 6:50 AM

# re: Oracle - Select a list of months for a Date Range
Requesting Gravatar...
Thats really a excellent query i would have wasted a lot of time without this thank u
Left by Ravikumar on Sep 04, 2009 10:18 AM

# re: Oracle - Select a list of months for a Date Range
Requesting Gravatar...
thnx buddy, was in very need of it
Left by sizzler on Sep 18, 2009 5:59 AM

# re: Oracle - Select a list of months for a Date Range
Requesting Gravatar...
Thank you so much. I was struggling to bring out this.
Left by Ponnusamy on Oct 04, 2009 12:19 AM

# re: Oracle - Select a list of months for a Date Range
Requesting Gravatar...
Excellent query, very helpful.
Left by MJ on Oct 08, 2009 1:24 PM

# re: Oracle - Select a list of months for a Date Range
Requesting Gravatar...
Very slick!
Left by Dave on Dec 15, 2009 12:38 PM

# re: Oracle - Select a list of months for a Date Range
Requesting Gravatar...
The following is better:

SELECT TO_CHAR(ADD_MONTHS(TRUNC(TO_DATE('30-10-2009', 'dd-mm-yyyy'), 'MM'), 1*LEVEL -1)) MONTH
FROM DUAL
CONNECT BY Level <= MONTHS_BETWEEN(TRUNC(TO_DATE ('02-12-2009', 'dd-mm-yyyy'),'MM'), TRUNC(TO_DATE ('22-10-2009', 'dd-mm-yyyy'),'MM')) + 1
ORDER BY MONTH
Left by date on Mar 15, 2010 5:49 AM

# re: Oracle - Select a list of months for a Date Range
Requesting Gravatar...
This is awesome !
Left by Tremis on May 28, 2010 9:19 AM

# re: Oracle - Select a list of months for a Date Range
Requesting Gravatar...
Thats is really cool.. but hwo to make it include nov 2008 ?
Left by srikanth on Jun 22, 2010 1:26 PM

# re: Oracle - Select a list of months for a Date Range
Requesting Gravatar...
wow, thank you very very much, this query help me very very very much.
Left by Hendra on Aug 30, 2010 8:37 PM

# re: Oracle - Select a list of months for a Date Range
Requesting Gravatar...
hen sha ..
Left by usa louis vuitton on Nov 12, 2010 2:05 AM

# re: Oracle - Select a list of months for a Date Range
Requesting Gravatar...
Hey dear your blog is very much rocking and stunning. This blog has main attraction and high quality features. Due to the latest posts, this blog is relatively famous among the readers of allover the world. You have provided enough information on many different topics
Left by Jordan retro sneakers on Dec 18, 2010 1:14 AM

# re: Oracle - Select a list of months for a Date Range
Requesting Gravatar...
This is really interesting. In a recent project, I coded each month in the drop-down list. I did not know you could simplify it this way. This is certainly useful. Keep up the great work.
Left by Travel Insurance on Feb 02, 2011 5:08 PM

# re: Oracle - Select a list of months for a Date Range
Requesting Gravatar...
great work...........
Left by Mayank on Aug 24, 2011 9:48 AM

# re: Oracle - Select a list of months for a Date Range
Requesting Gravatar...
Great work....

But can anybody help me to get this months order by
like

jan
feb
mar
apr
may
jun
jul
aug
sep
oct
nov
dec

thank you in advance
Left by surya on Oct 22, 2011 2:21 AM

# science
Requesting Gravatar...
Good work! I always like to leave comments whenever I see something unusual or impressive. I think we must appreciate those who do something especial. Keep it up, thanks
Helen Olsson.
Left by Helen Olsson on Oct 23, 2011 4:09 AM

# re: Oracle - Select a list of months for a Date Range
Requesting Gravatar...
This is a more elegent approach using keywords Month/Mon you could add Week, Quarter, etc.


select to_char(add_months (trunc (sysdate,'YYYY'), 1*Level -1),'Month') as
Month,to_char(add_months (trunc (sysdate,'YYYY'), 1*Level -1),'Mon') as mon , rownum as rn FROM Dual
CONNECT BY Level <= MONTHS_BETWEEN(add_months(trunc(sysdate,'YYYY'),13), trunc(sysdate,'YYYY') + 1)
order by rn
Left by Dave on Mar 13, 2012 4:09 PM

# re: Oracle - Select a list of months for a Date Range
Requesting Gravatar...
Actually like this alot better, cleaner less SQL


select ind.l as month_ind
,to_char(add_months(trunc(sysdate,'yyyy'), ind.l-1), 'Month') as month_descr
,to_char(add_months(trunc(sysdate,'yyyy'), ind.l-1), 'Mon') as mon

from dual descr,
(select l
from (select level l
from dual
connect by level <= 12
)
) ind
order by 1;
Left by Dave on Mar 13, 2012 4:20 PM

# re: Oracle - Select a list of months for a Date Range
Requesting Gravatar...
select date between date1 and date2 in all months want help me
Left by durai on Nov 27, 2012 4:35 AM

Your comment:
 (will show your gravatar)


Copyright © Rodney Vinyard | Powered by: GeeksWithBlogs.net