Home » SQL & PL/SQL » SQL & PL/SQL » Converting month/year to month number? (merged)
Converting month/year to month number? (merged) [message #335171] Mon, 21 July 2008 05:41 Go to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
Hi,


I hae month/year column,But i want to convert in to month number.

Which function we use to slove this issues.Can any one help me?

example: i have apr-2008,may-2008....etc.But i wan to see like month numbers
Re: Converting month/year to month number? [message #335174 is a reply to message #335171] Mon, 21 July 2008 05:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
TO_DATE and TO_CHAR functions.

Regards
Michel
Re: Converting month/year to month number? [message #335176 is a reply to message #335171] Mon, 21 July 2008 05:57 Go to previous messageGo to next message
ttparavindh
Messages: 22
Registered: December 2006
Location: Bangalore,India
Junior Member

Please refer given below links,

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements004.htm#sthref405

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions180.htm#i1009324

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions183.htm#i1003589

Best Regards,
Aravindh
Re: Converting month/year to month number? [message #335181 is a reply to message #335171] Mon, 21 July 2008 06:02 Go to previous messageGo to next message
jyothsna1612
Messages: 68
Registered: June 2008
Member
Hi
I think this hint will help you
SQL> SELECT TO_CHAR(TO_DATE('21-JUL-08','DD-MON-YY') ,'mm') "month"
  2  FROM DUAL;

mo
--
07


Thanks
Re: Converting month/year to month number? [message #335206 is a reply to message #335171] Mon, 21 July 2008 07:46 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
tondapi wrote on Mon, 21 July 2008 06:41
Hi,


I hae month/year column,But i want to convert in to month number.

Which function we use to slove this issues.Can any one help me?

example: i have apr-2008,may-2008....etc.But i wan to see like month numbers


Another asinine model by you.

Ok, now you are going to convert a DATE stored as a VARCHAR2 into another VARCHAR2 and when you try to sort, you are going to get this order:
1
10
11
12
2
3
4
5
6
7
8
9


Good move! Would you stop digging yourself deeper holes and create a proper data model? What is your background, law?
Problem in month-year column? [message #336542 is a reply to message #335171] Mon, 28 July 2008 01:50 Go to previous messageGo to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
Hi,

In my database, i have one column name is month-year. The values for this object is Apr 2008,mar 2008,may 2008......etc.


My problem is i want to divided month separately and year separately and i want to convert month in to month number.Which function we use to solve this issues?

Note: don't use extract
Re: Problem in month-year column? [message #336544 is a reply to message #336542] Mon, 28 July 2008 01:52 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
What is the datatype of your column if it is string then Never Store Dates as string.

Regards,
Rajat
Re: Problem in month-year column? [message #336545 is a reply to message #336544] Mon, 28 July 2008 01:56 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Use To_CHAR and TO_DATE functions.

Regards,
Rajat
Re: Problem in month-year column? [message #336550 is a reply to message #336542] Mon, 28 July 2008 02:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Note: don't use extract

Why? Is this a homework? And why do you create a new topic for the same question? It is the third time you ask the same thing are you unable to learn?

A date is a date and not a string or a couple of numbers.

Regards
Michel

[Updated on: Mon, 28 July 2008 02:13]

Report message to a moderator

Re: Problem in month-year column? [message #336552 is a reply to message #336545] Mon, 28 July 2008 02:14 Go to previous messageGo to next message
jyothsna1612
Messages: 68
Registered: June 2008
Member
Hi Tondapi,

This is a duplicate post
check this:

http://www.orafaq.com/forum/m/335181/126300/#msg_335181

Thanks
Re: Converting month/year to month number? (merged) [message #336695 is a reply to message #335171] Mon, 28 July 2008 13:18 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Always store a date as a date. If you want to store the current date as a month,

insert into my_table(a) values(trunc(sysdate,'month'));

select to_char(a,'MON-YYYY') FROM my_table;
> JUL-2008

select to_char(a,'MM') from my_table;
> 07
Previous Topic: Reconciliation of Transactions
Next Topic: Restricting a package from other users
Goto Forum:
  


Current Time: Fri Dec 09 21:38:16 CST 2016

Total time taken to generate the page: 0.12442 seconds