OraFAQ Forum: SQL & PL/SQL » How to store GMT timestamp in Database from SQL Developer import excelsheet

Home » SQL & PL/SQL » SQL & PL/SQL » How to store GMT timestamp in Database from SQL Developer import excelsheet (Oracle 11g R2)

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

How to store GMT timestamp in Database from SQL Developer import excelsheet [message #611205]

Sun, 30 March 2014 04:45

cpadesur
Messages: 2
Registered: March 2014
Location: Bangalore

Junior Member

Hello every one,

I am getting a excel sheet which contains many rows. In that there are some columns contains timestamp in GMT format.
ex. 03-02-2014 23:55:34 +0000.

I am importing this sheet in to Database using SQL Developer.
Please let me know which format I have to specify so that Database will accept the timestamp records.

Select to_timestamp('03-02-2014 23:55:34 +0000','MM-DD-YYYY HH24:MI:SS')
from dual;

Above query is not working because It has +0000 (GMT) time zone , I need to mention GMT timezone in the format.
I got stuck here please somebody help me.

Regards,
Chandru.

[EDITED by LF: fixed topic title typo; was "timesta,p"]

[Updated on: Sun, 30 March 2014 15:14] by Moderator

Report message to a moderator

Re: How to store GMT timesta,p in Database from SQL Developer import excelsheet [message #611206 is a reply to message #611205]

Sun, 30 March 2014 05:04

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

If you know that the last characters are always +0000 (time always from GMT) then you can use:

SQL> Select from_tz(to_timestamp('03-02-2014 23:55:34','MM-DD-YYYY HH24:MI:SS "+0000"'),'GMT') tim from dual;
TIM
---------------------------------------------------------------------------
02/03/2014 23:55:34.000 GMT

Report message to a moderator

Re: How to store GMT timesta,p in Database from SQL Developer import excelsheet [message #611210 is a reply to message #611206]

Sun, 30 March 2014 07:19

cpadesur
Messages: 2
Registered: March 2014
Location: Bangalore

Junior Member

Thanks Michel It is working fine.

It like below.

select to_timestamp('03-29-2014 23:58:05 +0000', 'MM-DD-YYYY HH24:MI:SS "+0000"')
from dual;

But I am facing anothere problem here.
My task is to import excel file in to DB using SQL Developer.
When I give format like MM-DD-YYYY HH24:MI:SS "+0000" in turn it is treating like to_timestamp('03-29-2014 23:58:05 +0000', 'MM-DD-YYYY HH24:MI:SS +0000') so it is giving error.

Please help me how can i mention format in SQL Developer.

Attachment: Capture.PNG
(Size: 61.77KB, Downloaded 541 times)

Report message to a moderator

Re: How to store GMT timesta,p in Database from SQL Developer import excelsheet [message #611211 is a reply to message #611210]

Sun, 30 March 2014 07:45

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

SQL> select to_timestamp_tz('03-29-2014 23:58:05 +0000', 'MM-DD-YYYY HH24:MI:SS +TZHTZM') from dual;

TO_TIMESTAMP_TZ('03-29-201423:58:05+0000','MM-DD-YYYYHH24:MI:SS+TZHTZM')
---------------------------------------------------------------------------
29-MAR-14 11.58.05.000000000 PM +00:00

SQL>

SY.

Report message to a moderator

Previous Topic:	ORA-00904: "CNT": invalid identifier error
Next Topic:	Case statement with null in where clause

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 03:37:34 CDT 2024