Home » SQL & PL/SQL » SQL & PL/SQL » Can We convert just a num [1-12] to [MON]
Can We convert just a num [1-12] to [MON] [message #292403] Tue, 08 January 2008 21:34 Go to next message
mm_kanish05
Messages: 487
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


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 Go to previous messageGo to next message
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,
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 Go to previous messageGo to next message
BlackSwan
Messages: 25039
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"
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
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
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 Go to previous messageGo to next message
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>
Smile

Thumbs Up
Rajuvan
Re: Can We convert just a num [1-12] to [MON] [message #292703 is a reply to message #292403] Wed, 09 January 2008 12:38 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Like Michael said, If you want the full month then

select to_char(to_date('&mm','MM'),'Month') from dual
Re: Can We convert just a num [1-12] to [MON] [message #293101 is a reply to message #292403] Thu, 10 January 2008 22:16 Go to previous message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

Hi Gurus,

Thank u for all ur post.

Wbr

Kanish
Previous Topic: Find duplicate rows in a table
Next Topic: Retrieve only duplicate records
Goto Forum:
  


Current Time: Tue Dec 06 06:38:45 CST 2016

Total time taken to generate the page: 0.15409 seconds