Home » SQL & PL/SQL » SQL & PL/SQL » How to get Month and Year between two date ranges
How to get Month and Year between two date ranges [message #294543] Thu, 17 January 2008 23:48 Go to next message
Zafarul Islam
Messages: 3
Registered: August 2007
Junior Member
How to get Month and year between two date ranges
e.g
suppose i have 2 dates

from date : 01/01/2006
to date : 01/01/2010

so how can i get

Jan-06 , Feb-06 , Mar-06 ............. Jan-10

Please suggest and help for the same

Ansari Zafarul Islam

Re: How to get Month and Year between two date ranges [message #294567 is a reply to message #294543] Fri, 18 January 2008 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for "calendar" and/or "row generator".

Regards
Michel
Re: How to get Month and Year between two date ranges [message #295036 is a reply to message #294543] Mon, 21 January 2008 03:18 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

Hi

Tell me Why it is required or which context it will necessary


by

kanish
Re: How to get Month and Year between two date ranges [message #295041 is a reply to message #295036] Mon, 21 January 2008 03:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Calendar is often use to fill data when there is not a line per date/week/month/year.
For instance, assume we insert a line in "sales" table each time we sell an article. We want the number of saled articles per week but some weeks we don't sale any and for these weeks we need to display a line with 0.
In this case, we need a calendar.

Regards
Michel
Re: How to get Month and Year between two date ranges [message #295056 is a reply to message #294543] Mon, 21 January 2008 03:59 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

hi

did u need like this sorry if any mistakes.


select to_char(sys,'MON')||'-'||to_char(sys,'YYYY') from 
(select to_date('01-jan-08') sys from dual
union
select sysdate as sys  from dual)

Re: How to get Month and Year between two date ranges [message #295058 is a reply to message #295056] Mon, 21 January 2008 04:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know the purpose of your query but this gives the following on my database:
SQL> select to_char(sys,'MON')||'-'||to_char(sys,'YYYY') from 
  2  (select to_date('01-jan-08') sys from dual
  3  union
  4  select sysdate as sys  from dual)
  5  /
(select to_date('01-jan-08') sys from dual
                *
ERROR at line 2:
ORA-01858: a non-numeric character was found where a numeric was expected

Regards
Michel
Re: How to get Month and Year between two date ranges [message #295060 is a reply to message #294543] Mon, 21 January 2008 04:11 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

hi

try like this

SQL> select to_char(sys,'MON')||'-'||to_char(sys,'YYYY') from 
  2     (select to_date('01-jan-2008') sys from dual
  3     union
  4     select sysdate as sys  from dual)
  5  ;

TO_CHAR(
--------
JAN-2008
JAN-2008


kanish
Re: How to get Month and Year between two date ranges [message #295061 is a reply to message #295060] Mon, 21 January 2008 04:15 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the difference with what I posted?

An advice: NEVER rely on implicit conversion or format.

In addition, what is the purpose of your query?

Regards
Michel
Previous Topic: to write sql query
Next Topic: dbms_aq-package state invalidated
Goto Forum:
  


Current Time: Thu Dec 08 18:03:10 CST 2016

Total time taken to generate the page: 0.08978 seconds