Home » SQL & PL/SQL » SQL & PL/SQL » how to convert a timestamp with time zone into a date?
how to convert a timestamp with time zone into a date? [message #214900] Thu, 18 January 2007 08:10 Go to next message
aline
Messages: 92
Registered: February 2002
Member
Hello,

I'm currently saving a timestamp with time zone from a client in Paris into a american server.

How can I retrieve the local information?

SQL> create table test
  2  (
  3    aa     TIMESTAMP(6) WITH TIME ZONE
  4  )
  5   ;

Table created

SQL>  insert into test values (sysdate);

1 row inserted

SQL>  select * from test;

AA
-------------------------------------------------
18-JAN-07 09.08.42.000000 AM +01:00

SQL>  SELECT TO_CHAR(aa, 'dd-mon-yyyy hh24:mi:ss')
  2  FROM test;

TO_CHAR(AA,'DD-MON-YYYYHH24:M
---------------------------------------------------------------------------
18-jan-2007 09:08:42

SQL>  SELECT  ?????????   FROM TEST;

18-jan-2007 15:08:42



Re: how to convert a timestamp with time zone into a date? [message #262653 is a reply to message #214900] Tue, 28 August 2007 00:43 Go to previous messageGo to next message
hasan_uiu
Messages: 18
Registered: August 2007
Location: Dhaka
Junior Member

use TO_TIMESTAMP_TZ

SELECT TO_TIMESTAMP_TZ('2004-12-01 11:00:00 -8:00',
'YYYY-MM-DD HH:MI:SS TZH:TZM') FROM dual;
Re: how to convert a timestamp with time zone into a date? [message #262671 is a reply to message #214900] Tue, 28 August 2007 01:08 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table test (aa TIMESTAMP(6) WITH TIME ZONE);

Table created.

SQL> insert into test values (sysdate);

1 row created.

SQL> select * from test;
AA
---------------------------------------------------------------------------
28/08/2007 08:07:29.000 +02:00

1 row selected.

SQL> SELECT TO_CHAR(aa, 'dd-mon-yyyy hh24:mi:ss tzr') from test;
TO_CHAR(AA,'DD-MON-YYYYHH24:MI:SSTZR')
-----------------------------------------------------
28-aug-2007 08:07:29 +02:00

1 row selected.

Datetime Format Models
Regards
Michel
Previous Topic: how we can do manipulations like aggregration functions or any manipulation
Next Topic: how can we access server which is in unix environment
Goto Forum:
  


Current Time: Sun Dec 04 08:50:50 CST 2016

Total time taken to generate the page: 0.14215 seconds