Home » SQL & PL/SQL » SQL & PL/SQL » how to retrieve date with timezone
how to retrieve date with timezone [message #348521] Wed, 17 September 2008 01:59 Go to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
Need to retrieve a date in this format : Sat Sep 12 04:10:33 GMT-08:00 1970

Can you please give me the query?
Re: how to retrieve date with timezone [message #348522 is a reply to message #348521] Wed, 17 September 2008 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Datetime Format Models

Good exercise to learn them.

Regards
Michel
Re: how to retrieve date with timezone [message #348530 is a reply to message #348522] Wed, 17 September 2008 02:47 Go to previous messageGo to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
thanks for your reply.
i was trying to use the format HH:MI:SS.FFTZH:TZR format but I m getting ora-01821 date format not recognized error.Can u please help..

Regards,
Navneet
Re: how to retrieve date with timezone [message #348532 is a reply to message #348530] Wed, 17 September 2008 02:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste your session.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: how to retrieve date with timezone [message #348535 is a reply to message #348532] Wed, 17 September 2008 02:56 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
You have to use
SYSTIMESTAMP
to get the fractional seconds instead of
SYSDATE
Re: how to retrieve date with timezone [message #348537 is a reply to message #348532] Wed, 17 September 2008 02:59 Go to previous messageGo to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member

I was trying like that as per the link suggested.

SQL> select to_char(sysdate,'dd-mon-yyyy HH:MI:SS.FFTZH:TZM') from dual;

select to_char(sysdate,'dd-mon-yyyy HH:MI:SS.FFTZH:TZM') from dual

ORA-01821: date format not recognized

SQL>

Regards,
Navneet
Re: how to retrieve date with timezone [message #348539 is a reply to message #348537] Wed, 17 September 2008 03:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As @_jum said:
Quote:
You have to use
SYSTIMESTAMP
to get the fractional seconds instead of
SYSDATE


Try this:
 select to_char(systimestamp,'dd-mon-yyyy HH:MI:SS.FFTZH:TZM') from dual;
Re: how to retrieve date with timezone [message #348541 is a reply to message #348535] Wed, 17 September 2008 03:10 Go to previous messageGo to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
Thanks
select to_char(systimestamp,'dd-mon-yyyy HH:MI:SS.FFTZH:TZM') from dual; is working.Now i will try to format my output as

Sat Sep 12 04:10:33 GMT-08:00 1970

thanks a ton again Smile
Regards,
Navneet
Re: how to retrieve date with timezone [message #348547 is a reply to message #348537] Wed, 17 September 2008 03:38 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As @_jum said:
Quote:
You have to use
SYSTIMESTAMP
to get the fractional seconds instead of
SYSDATE


Try this:
 select to_char(systimestamp,'dd-mon-yyyy HH:MI:SS.FFTZH:TZM') from dual;
Previous Topic: need a help
Next Topic: select into query
Goto Forum:
  


Current Time: Mon Dec 05 13:15:47 CST 2016

Total time taken to generate the page: 0.32080 seconds