OraFAQ Forum: SQL & PL/SQL » convert YYYY-MM-DDTHH:MM:SS to YYYY-MM-DD HH:MM:SS

Home » SQL & PL/SQL » SQL & PL/SQL » convert YYYY-MM-DDTHH:MM:SS to YYYY-MM-DD HH:MM:SS

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

convert YYYY-MM-DDTHH:MM:SS to YYYY-MM-DD HH:MM:SS [message #225614]

Tue, 20 March 2007 09:33

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.

Report message to a moderator

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

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

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

Report message to a moderator

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

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

Report message to a moderator

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

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

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

Report message to a moderator

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

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

Report message to a moderator

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

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.

Report message to a moderator

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

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

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

Report message to a moderator

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

gururajd
Messages: 7
Registered: June 2006
Location: Chennai

Junior Member

It works !!!
Thanks Michel for your kind and patient responce.

Report message to a moderator

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

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

Report message to a moderator

Previous Topic:	Delete all records from all tables
Next Topic:	inserting multiple rows in single query..

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Sep 01 05:30:47 CDT 2025