Home » SQL & PL/SQL » SQL & PL/SQL » proble in sql?
proble in sql? [message #275614] Mon, 22 October 2007 01:38 Go to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
Hi

I am ne to sql, my problem is i want to convert mmyyyy in to jan
2007.

anyone give me idea?
Re: proble in sql? [message #275615 is a reply to message #275614] Mon, 22 October 2007 01:41 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
first post what you tried?
Re: proble in sql? [message #275616 is a reply to message #275615] Mon, 22 October 2007 01:43 Go to previous messageGo to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
select to_date(2007,"mmyyyy") dual;
Re: proble in sql? [message #275618 is a reply to message #275614] Mon, 22 October 2007 01:45 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Take help of to_char function.
Re: proble in sql? [message #275620 is a reply to message #275615] Mon, 22 October 2007 01:51 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Idea 1: Start typing all the characters. From someone living in the USA one would expect that he knows how to write in English.

Ok, so you want to convert one date format to another. Make sure the input is a date (TO_DATE if necessary) and convert that to a VARCHAR2 string using TO_CHAR with the proper date format.
TO_CHAR(yourdate,'yourdateformat')
will do the trick. Details can be found in the Oracle online documentation. Have a look at the forum guidelines. You should find a link in there.

MHE
Re: proble in sql? [message #275623 is a reply to message #275616] Mon, 22 October 2007 01:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
tondapi wrote on Mon, 22 October 2007 08:43

select to_date(2007,"mmyyyy") dual;

The to_date function takes two parameters of type string. You provide a number and a string.
The second parameter is the date format mask, and it indicates how to interpret the first string. Therefore, it should have the same number of characters as the first parameter.

So, suppose you only have a year and you want to convert it to a string 'Jan 2007'
Suppose you only would provide the year:
SQL> select to_date('2007', 'yyyy') from dual;

TO_DATE('
---------
01-OCT-07

Now, that takes the current month as the default. Not what you want, so you have to explicitly indicate you want the first month of that year:
SQL> select to_date('200701', 'yyyymm') from dual;

TO_DATE('
---------
01-JAN-07

Ok, nearly there. I leave it to you how to get from this date to the string you want (hint: use to_char)

[edit: bummer, got distracted while typing the answer. Now Maarten has already answered the same.]

[Updated on: Mon, 22 October 2007 02:00]

Report message to a moderator

Re: proble in sql? [message #275624 is a reply to message #275614] Mon, 22 October 2007 02:01 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
select to_char(trunc(to_date('2007','yyyy'),'yyyy'),'MONyyyy') 
from dual
Re: proble in sql? [message #275627 is a reply to message #275624] Mon, 22 October 2007 02:05 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
muzahidul islam: the solution provider for newbies.

Don't you think to create a web site. I give you the motto:
"Students, I do your homeworks, post them here"

Regards
Michel

[Updated on: Mon, 22 October 2007 02:07]

Report message to a moderator

Previous Topic: Pl/Sql code
Next Topic: Dynamic declaration of %rowtype
Goto Forum:
  


Current Time: Fri Dec 09 21:36:08 CST 2016

Total time taken to generate the page: 0.09580 seconds