Home » SQL & PL/SQL » SQL & PL/SQL » convert YYYY-MM-DDTHH:MM:SS to YYYY-MM-DD HH:MM:SS
convert YYYY-MM-DDTHH:MM:SS to YYYY-MM-DD HH:MM:SS [message #225614] Tue, 20 March 2007 09:33 Go to next message
gururajd
Messages: 7
Registered: June 2006
Location: Chennai
Junior Member
I am using oracle 9i and curretly a timestamp field is storing timestamp as YYYY-MM-DD HH:MM:SS .

I wanted to convert this timestamp field to return the timestamp as YYYY-MM-DDTHH:MM:SS or YYYY-MM-DDTHH:MM:SS.S. format.

How is it possible???

Thanks.
Re: convert YYYY-MM-DDTHH:MM:SS to YYYY-MM-DD HH:MM:SS [message #225616 is a reply to message #225614] Tue, 20 March 2007 09:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Is your field a real timestamp or a varchar2?

In the former use to_char(timestamp_field,'YYYY-MM-DD"T"HH:MM:SS').
In the latter use replace(timestamp_field,' ','T').

Regards
Michel

Re: convert YYYY-MM-DDTHH:MM:SS to YYYY-MM-DD HH:MM:SS [message #225620 is a reply to message #225614] Tue, 20 March 2007 09:56 Go to previous messageGo to next message
gururajd
Messages: 7
Registered: June 2006
Location: Chennai
Junior Member
Thanks for the reply Michel,
It works!!!

now what if i have input string as YYYY-MM-DDTHH:MM:SSZ and i need to store it into the same timestamp field

Thanks
Re: convert YYYY-MM-DDTHH:MM:SS to YYYY-MM-DD HH:MM:SS [message #225623 is a reply to message #225620] Tue, 20 March 2007 10:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Replace to_char to to_timestamp but I don't understand your point.
If the field is a real timestamp that is one of the TIMESTAMP datatype it is not stored in a format or another one, it is stored in an internal format and only when you query it is formated as you want. "Human" format is irrelevant for a timestamp or date field storage.

Regards
Michel
Re: convert YYYY-MM-DDTHH:MM:SS to YYYY-MM-DD HH:MM:SS [message #225626 is a reply to message #225614] Tue, 20 March 2007 10:45 Go to previous messageGo to next message
gururajd
Messages: 7
Registered: June 2006
Location: Chennai
Junior Member
sorry if am not clear

i have a string '2007-03-02T12:58:16Z' and need to insert this value into a table where the column datatype is timestamp.


Hope now i have explained clearly.

Thanks
Re: convert YYYY-MM-DDTHH:MM:SS to YYYY-MM-DD HH:MM:SS [message #225627 is a reply to message #225614] Tue, 20 March 2007 10:54 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
try this site:

http://www.techonthenet.com/oracle/functions/to_date.php

for a very brief explanation.

If you need more, do a google on Oracle and To_Date.

Re: convert YYYY-MM-DDTHH:MM:SS to YYYY-MM-DD HH:MM:SS [message #225630 is a reply to message #225626] Tue, 20 March 2007 11:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use TO_TIMESTAMP function:
SQL> select to_timestamp('2007-03-02T12:58:16Z','YYYY-MM-DD"T"HH24:MI:SS"Z"') from dual;
TO_TIMESTAMP('2007-03-02T12:58:16Z','YYYY-MM-DD"T"HH24:MI:SS"Z"')
---------------------------------------------------------------------------
02/03/2007 12:58:16.000

1 row selected.

The result is in my default timestamp output format but it does not matter.

Regards
Michel
icon14.gif  Re: convert YYYY-MM-DDTHH:MM:SS to YYYY-MM-DD HH:MM:SS [message #225631 is a reply to message #225614] Tue, 20 March 2007 11:18 Go to previous messageGo to next message
gururajd
Messages: 7
Registered: June 2006
Location: Chennai
Junior Member
It works !!!
Thanks Michel for your kind and patient responce.

Re: convert YYYY-MM-DDTHH:MM:SS to YYYY-MM-DD HH:MM:SS [message #225658 is a reply to message #225614] Tue, 20 March 2007 15:01 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Just curious,
why did you want to replace the space with a "T" before you converted it to a timestamp. You could have just as easily done

select to_timestamp('2007-03-02 12:58:16Z','YYYY-MM-DD HH24:MI:SS"Z"') from dual;
Previous Topic: Delete all records from all tables
Next Topic: inserting multiple rows in single query..
Goto Forum:
  


Current Time: Tue Dec 06 06:43:23 CST 2016

Total time taken to generate the page: 0.15190 seconds