Home » Other » Training & Certification » month and month number
month and month number [message #289964] Wed, 26 December 2007 22:55 Go to next message
annu-agi
Messages: 203
Registered: July 2005
Location: Karachi
Senior Member

hi experts

i have a question to obtain months and month numbers from dual, i tried alot but cant make a query . because i think we dont have any store data. and if using sysdate, it gives me only current date..and if i define variables like st_Date=01-jan-2007 and ed_date=31-dec-2007 then it might give me 2 months but all rest of months ignore because it doesnt store anywhere.
May be logically it possible. but how, i dont know. Is there anybody who can make this query. I need query result like below

January 01
February 02
March 03
April 04
May 05
June 06
July 07
August 08
September 09
October 10
November 11
December 12


regards


Anwer
Re: month and month number [message #289966 is a reply to message #289964] Wed, 26 December 2007 22:58 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>i have a question to obtain months and month numbers from dual
Why must the results come from dual?
Re: month and month number [message #289969 is a reply to message #289964] Wed, 26 December 2007 23:07 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Any way try luck using

Dual .. Connect by .. level combination

Thumbs Up
Rajuvan.
Re: month and month number [message #289971 is a reply to message #289966] Wed, 26 December 2007 23:08 Go to previous messageGo to next message
annu-agi
Messages: 203
Registered: July 2005
Location: Karachi
Senior Member

thanks for a concern ...


its a requirment or you can say its a assignment to have results from dual or without creating any table. to prove is it possible or not ??

regards

anwer
Re: month and month number [message #289979 is a reply to message #289971] Thu, 27 December 2007 00:26 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There are variations to the subject; all of them end up with the row generator techniques. Here are two of them; explore the rest by yourself.
SELECT 
  TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'yyyy'), LEVEL - 1), 'month') mon_1,
  TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'yyyy'), LEVEL - 1), 'mm') mon_2 
FROM dual
CONNECT BY LEVEL <= 12
ORDER BY 2;

WITH YEAR AS
 (SELECT ADD_MONTHS(TRUNC(SYSDATE, 'yyyy'), LEVEL - 1) mon
  FROM dual
  CONNECT BY LEVEL <= 12
 )
  SELECT TO_CHAR(mon, 'month') mon_1,
         TO_CHAR(mon, 'mm') mon_2 
  FROM YEAR
  ORDER BY 2;
Previous Topic: increment months
Next Topic: If I use Put_Line Then program get's compiled but o/p is not shown.
Goto Forum:
  


Current Time: Fri Dec 09 00:19:08 CST 2016

Total time taken to generate the page: 0.13911 seconds