OraFAQ Forum: SQL & PL/SQL » Can We convert just a num [1-12] to [MON]

Home » SQL & PL/SQL » SQL & PL/SQL » Can We convert just a num [1-12] to [MON]

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

Can We convert just a num [1-12] to [MON] [message #292403]

Tue, 08 January 2008 21:34

mm_kanish05
Messages: 493
Registered: January 2007
Location: Chennai

Senior Member

Hi Gurus,

I need to convert a number which between 1 and 12 to 'Mon' format or January,April like..

  select  substr((to_date(to_char('01/'||4||'/2007'),'dd/mm/yyyy')),4,3) from dual;

SUB
---
APR

I need anyother way or any function for the same.

wbr.

Kanish

Report message to a moderator

Re: Can We convert just a num [1-12] to [MON] [message #292448 is a reply to message #292403]

Tue, 08 January 2008 23:45

dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai

Senior Member

why not just use TO_CHAR().are you looking to do something like this.

SELECT to_char(to_date('09/01/2008','dd/mm/yyyy'),'MON') from dual

regards,

Report message to a moderator

Re: Can We convert just a num [1-12] to [MON] [message #292453 is a reply to message #292403]

Tue, 08 January 2008 23:53

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I need to convert a number which between 1 and 12 to 'Mon' format or January,April like..

Quote:

SELECT to_char(to_date('09/01/2008','dd/mm/yyyy'),'MON') from dual

RESULTS IN

SQL> SELECT to_char(to_date('09/01/2008','dd/mm/yyyy'),'MON') from dual;

TO_
---
JAN

which does not exactly meet requirements of "or January,April like"

Report message to a moderator

Re: Can We convert just a num [1-12] to [MON] [message #292454 is a reply to message #292453]

Wed, 09 January 2008 00:02

dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai

Senior Member

this is what the OP's output showed.converting a numeric month format into a three lettes month format.or are you trying to say something else.

maybe this :

SELECT to_char(to_date('09/01/2008','dd/mm/yyyy'),'MONTH') from dual

regards,

[Updated on: Wed, 09 January 2008 00:03]

Report message to a moderator

Re: Can We convert just a num [1-12] to [MON] [message #292481 is a reply to message #292403]

Wed, 09 January 2008 00:58

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> def mm=1
SQL> select to_char(to_date('&mm','MM'),'MON') from dual;
TO_
---
JAN

1 row selected.

SQL> def mm=4
SQL> /
TO_
---
APR

1 row selected.

SQL> def mm=10
SQL> /
TO_
---
OCT

1 row selected.

Regards
Michel

Report message to a moderator

Re: Can We convert just a num [1-12] to [MON] [message #292496 is a reply to message #292403]

Wed, 09 January 2008 01:21

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

Senior Member

It is nice to see the Answers trying to satisfy OP's exact requirement . Then One answer from my side also .

Quote:

number which between 1 and 12 to 'Mon' format or January,April like..

 SQL> SELECT to_char(to_date('09/01/2008','dd/mm/yyyy'),'Month') from dual;

TO_CHAR(T
---------
January

SQL> SELECT to_char(to_date('01/04/2007','dd/mm/yyyy'),'Month') from dual;

TO_CHAR(T
---------
April

SQL>