Home » SQL & PL/SQL » SQL & PL/SQL » Date format conversion.
Date format conversion. [message #221606] Tue, 27 February 2007 11:49 Go to next message
hariyadav
Messages: 12
Registered: February 2007
Location: hyderabad
Junior Member
Hi All,

Good morning,

I have a problem while converting date from one format to another format.

I need to convert 'YYMMDD' format to 'MON DD,YYYY' format.

Example:
I have date like: '971025' it needs to convert 'OCT 25,19997'.

Can any one please help me.

Thanks,
Hari Yadav.G
Re: Date format conversion. [message #221607 is a reply to message #221606] Tue, 27 February 2007 11:55 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
If it's a DATE column, then just use
to_char(column,'MON DD,YYYY');

If it's in a VARCHAR2 column, which is your mistake, then you'll have to convert it to a DATE first, which will most likely fail on some of your rows. First use
to_date(column,'RRMMDD')
Re: Date format conversion. [message #221609 is a reply to message #221607] Tue, 27 February 2007 12:10 Go to previous messageGo to next message
hariyadav
Messages: 12
Registered: February 2007
Location: hyderabad
Junior Member
My date colum is VARCHAR2 column.
I try to_date(column,'RRMMDD'), it returns date as 'DD-MON-YY' format.

Please let me know. Is there any alternative to solve this?

Thanks,
Sreehari.G
Re: Date format conversion. [message #221610 is a reply to message #221609] Tue, 27 February 2007 12:17 Go to previous messageGo to next message
hariyadav
Messages: 12
Registered: February 2007
Location: hyderabad
Junior Member
I got it,

I solve my problem as follows:

declare
dt date;
res varchar2(20);
Begin
select to_date('971025','YYMMDD') into dt from dual;
select to_char(dt,'MON DD,YYYY') into res from dual;
dbms_output.put_line(res);
End;

Thanks for your help..

Hari Yadav.G
Re: Date format conversion. [message #221625 is a reply to message #221610] Tue, 27 February 2007 16:39 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
hariyadav wrote on Tue, 27 February 2007 12:17
I got it,

I solve my problem as follows:

declare
dt date;
res varchar2(20);
Begin
select to_date('971025','YYMMDD') into dt from dual;
select to_char(dt,'MON DD,YYYY') into res from dual;
dbms_output.put_line(res);
End;

Thanks for your help..

Hari Yadav.G

Combine them

select to_char(to_date('971025','RRMMDD'),'Mon DD, YYYY') from dual;

[Updated on: Tue, 27 February 2007 16:39]

Report message to a moderator

Re: Date format conversion. [message #221647 is a reply to message #221625] Tue, 27 February 2007 22:03 Go to previous messageGo to next message
hariyadav
Messages: 12
Registered: February 2007
Location: hyderabad
Junior Member

declare
dt date;
res varchar2(20);
Begin
select to_date('971025','YYMMDD') into dt from dual;
select to_char(dt,'MON DD,YYYY') into res from dual;
dbms_output.put_line(res);
End;

The above code returns invalid year. It should return 1997.
OCT 25,2097

Please help me out.

Thanks in advance,
Hari Yadav.G
Re: Date format conversion. [message #221648 is a reply to message #221606] Tue, 27 February 2007 22:19 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
>Please help me out.
Is the 1st thing you do when don't get the output you want, post on ORAFAQ?
What is wrong with YOU helping yourself?
Have you EVER read the SQL Reference Manual once, such as?
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements004.htm#sthref413
Re: Date format conversion. [message #221651 is a reply to message #221625] Tue, 27 February 2007 22:25 Go to previous messageGo to next message
kkinapps
Messages: 29
Registered: November 2006
Location: Hyderabad
Junior Member

Quote:


select to_char(to_date('971025','RRMMDD'),'Mon DD, YYYY') from dual;


Use the above statement as suggested by Bill. I think it solves your issue, have you tried using that.

Regards
KK
Re: Date format conversion. [message #221704 is a reply to message #221647] Wed, 28 February 2007 02:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your mistake is to use the YY date mask with a two digit year.
You should try using @Bill B's solution, which uses the RR format instead.
Re: Date format conversion. [message #221706 is a reply to message #221704] Wed, 28 February 2007 02:21 Go to previous messageGo to next message
hariyadav
Messages: 12
Registered: February 2007
Location: hyderabad
Junior Member
I got it.

Thank you very much for your great help.
Re: Date format conversion. [message #221716 is a reply to message #221706] Wed, 28 February 2007 03:09 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
extend BillB's solution one step further: no need to select from dual. A simple assignment will do:
declare
  res varchar2(20);
begin
  res := to_char(to_date('971025','RRMMDD'),'Mon DD, YYYY');
  dbms_output.put_line(res);
end;
Previous Topic: How to get the Months (Jan to Dec) of the year through SQL Query?
Next Topic: Write to a text file on remote server
Goto Forum:
  


Current Time: Mon Dec 05 09:11:12 CST 2016

Total time taken to generate the page: 0.11182 seconds