OraFAQ Forum: Training & Certification » month and month number

Home » Other » Training & Certification » month and month number

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

month and month number [message #289964]

Wed, 26 December 2007 22:55

annu-agi
Messages: 238
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

Report message to a moderator

Re: month and month number [message #289966 is a reply to message #289964]

Wed, 26 December 2007 22:58

BlackSwan
Messages: 26766
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?

Report message to a moderator

Re: month and month number [message #289969 is a reply to message #289964]

Wed, 26 December 2007 23:07

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

Any way try luck using

Dual .. Connect by .. level combination

Thumbs Up

Rajuvan.

Report message to a moderator

Re: month and month number [message #289971 is a reply to message #289966]

Wed, 26 December 2007 23:08

annu-agi
Messages: 238
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

Report message to a moderator

Re: month and month number [message #289979 is a reply to message #289971]

Thu, 27 December 2007 00:26

Littlefoot
Messages: 21823
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;

Report message to a moderator

Previous Topic:	increment months
Next Topic:	If I use Put_Line Then program get's compiled but o/p is not shown.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 30 20:27:57 CST 2025