Home » SQL & PL/SQL » SQL & PL/SQL » VARCHAR2 to Date Format Conversion
VARCHAR2 to Date Format Conversion [message #425259] Thu, 08 October 2009 05:38 Go to next message
SanthoshKumar_s
Messages: 28
Registered: March 2005
Location: Hyderabad
Junior Member

Hi gurus,

Here is my Query:

SELECT attribute7
FROM test_table

Output is as below : 2009/10/15 00:00:00

Note: Datatype of attribute7 is VARCHAR2.

Requirement : The value stored in the attribute7 needs to be changed as '15-OCT-2009'

Any Inputs on how to achieve this.

Thanks,
Santhosh
Re: VARCHAR2 to Date Format Conversion [message #425261 is a reply to message #425259] Thu, 08 October 2009 05:42 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Use format model


If its a date always store as date data type.
Re: VARCHAR2 to Date Format Conversion [message #425262 is a reply to message #425261] Thu, 08 October 2009 05:47 Go to previous messageGo to next message
SanthoshKumar_s
Messages: 28
Registered: March 2005
Location: Hyderabad
Junior Member

Dear Olivia,

Thanks for your quick turn around and suggestion.

Of course, if we are creating a new one, we can follow the said guidelines.
But this is in one of the clients database instance and issue needs to be solved.

Thanks,
Santhosh
Re: VARCHAR2 to Date Format Conversion [message #425265 is a reply to message #425259] Thu, 08 October 2009 06:06 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member


update
test_table
set attribute7 = '15-OCT-2009'
where  attribute7  = '2009/10/15 00:00:00'



and then you might want to read as to why EAV Model is a bad design?

[Updated on: Thu, 08 October 2009 06:07]

Report message to a moderator

Re: VARCHAR2 to Date Format Conversion [message #425266 is a reply to message #425259] Thu, 08 October 2009 06:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
UPDATE test_table
SET attribute7 = to_char(to_date(attribute7,'yyyy/mm/dd hh:mi:ss'),'dd-MON-rr')
Re: VARCHAR2 to Date Format Conversion [message #425267 is a reply to message #425259] Thu, 08 October 2009 06:15 Go to previous messageGo to next message
SanthoshKumar_s
Messages: 28
Registered: March 2005
Location: Hyderabad
Junior Member

Thanks JROW and Bonker for your inputs..

Since it is Client Instance, we are not supposed and permitted to do any Updates on the tables.

Instead the only possible way is how best can we convert the existing data and use it.
Re: VARCHAR2 to Date Format Conversion [message #425269 is a reply to message #425267] Thu, 08 October 2009 06:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SanthoshKumar_s wrote on Thu, 08 October 2009 13:15
Thanks JROW and Bonker for your inputs..

Since it is Client Instance, we are not supposed and permitted to do any Updates on the tables.

Instead the only possible way is how best can we convert the existing data and use it.

So do it on select.

Regards
Michel

Re: VARCHAR2 to Date Format Conversion [message #425270 is a reply to message #425267] Thu, 08 October 2009 06:29 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Quote:
Requirement : The value stored in the attribute7 needs to be changed as '15-OCT-2009'


It looked liked you wanted to update the value that was currently being stored in the table. However if you want to use only in select then it can be just

select case when test attribute7  = '2009/10/15 00:00:00'
then '15-Oct-2009' else attribute7 end newattribute7 from test_table





Re: VARCHAR2 to Date Format Conversion [message #425284 is a reply to message #425259] Thu, 08 October 2009 07:05 Go to previous message
sen_sam86
Messages: 33
Registered: August 2009
Location: Chennai
Member
select to_char(sysdate,'dd-mon-yyyy') FROM dual;


Are u expecting this kind of result
Previous Topic: how can i return the resultset directly to the calling code
Next Topic: :NEW in cursor ?
Goto Forum:
  


Current Time: Sat Dec 03 12:27:56 CST 2016

Total time taken to generate the page: 0.04226 seconds