OraFAQ Forum: SQL & PL/SQL » Converting month/year to month number? (merged)

Home » SQL & PL/SQL » SQL & PL/SQL » Converting month/year to month number? (merged)

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

Converting month/year to month number? (merged) [message #335171]

Mon, 21 July 2008 05:41

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

Report message to a moderator

Re: Converting month/year to month number? [message #335174 is a reply to message #335171]

Mon, 21 July 2008 05:55

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

Senior Member
Account Moderator

TO_DATE and TO_CHAR functions.

Regards
Michel

Report message to a moderator

Re: Converting month/year to month number? [message #335176 is a reply to message #335171]

Mon, 21 July 2008 05:57

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

Report message to a moderator

Re: Converting month/year to month number? [message #335181 is a reply to message #335171]

Mon, 21 July 2008 06:02

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

Report message to a moderator

Re: Converting month/year to month number? [message #335206 is a reply to message #335171]

Mon, 21 July 2008 07:46

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

tondapi wrote on Mon, 21 July 2008 06:41

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:

Good move! Would you stop digging yourself deeper holes and create a proper data model? What is your background, law?

Report message to a moderator

Problem in month-year column? [message #336542 is a reply to message #335171]

Mon, 28 July 2008 01:50

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

Report message to a moderator

Re: Problem in month-year column? [message #336544 is a reply to message #336542]

Mon, 28 July 2008 01:52

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

Report message to a moderator

Re: Problem in month-year column? [message #336545 is a reply to message #336544]

Mon, 28 July 2008 01:56

rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA

Senior Member

Use To_CHAR and TO_DATE functions.

Regards,
Rajat

Report message to a moderator

Re: Problem in month-year column? [message #336550 is a reply to message #336542]

Mon, 28 July 2008 02:12

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

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

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

Report message to a moderator

Re: Converting month/year to month number? (merged) [message #336695 is a reply to message #335171]

Mon, 28 July 2008 13:18

Bill B
Messages: 1971
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

Report message to a moderator

Previous Topic:	Reconciliation of Transactions
Next Topic:	Restricting a package from other users

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 09 18:59:25 CST 2024