How do i convert oracle sysdate with GMT: Oracle date formate

by: AndytwPosted on 2009-08-05 at 06:48:53ID: 25023390

Try this:

select to_char(systimestamp, 'Dy Mon DD HH24:MI:SS TZR YYYY') as "Current Time"
from dual

1:
2:

Select all Open in new window

by: MarkusIdPosted on 2009-08-05 at 06:50:35ID: 25023415

select to_char(systimestamp, 'Dy Mon DD HH24:MI:SS ') || 
       extract(timezone_abbr from systimestamp) || 
       to_char(sysdate, ' YYYY') as "Current Time"
from dual
/

1:
2:
3:
4:
5:

Select all Open in new window

by: johnsonePosted on 2009-08-05 at 06:54:10ID: 25023454

I believe you want TZD. TZD is the abreviated form for TZR.

by: delmandiyarPosted on 2009-08-05 at 07:05:22ID: 25023559

Thank you for the quick answare:
I already trid tthat: the problem is that i have a table that saves data from a strange application. in the table a have a column called Date, but column type is varchar and the date that are saved have this formate:

Mon Aug 03 09:49:48 GMT 2009

the qustion is if i select this column from the table and compare it with another oracle datae such a sysdate, how could i do it??

your select which i already have trid giveng me:
Ons Aug 05 16:28:03 +02:00 2009
that +02:00 is different from GMT withc is the name of time zone.

by: MarkusIdPosted on 2009-08-05 at 07:13:28ID: 25023644

Hi,

have you tried my solution? This should give the timezone (unfortunaelty
it is set to unknown on our test-db:

SQL> select to_char(systimestamp, 'Dy Mon DD HH24:MI:SS ') ||
  2         extract(timezone_abbr from systimestamp) ||
  3         to_char(sysdate, ' YYYY') as "Current Time"
  4  from dual
  5  /
 
Current Time
----------------------------------
We Aug 05 16:12:17 UNK 2009

1:
2:
3:
4:
5:
6:
7:
8:
9:

Select all Open in new window

by: johnsonePosted on 2009-08-05 at 07:35:38ID: 25023904

It appears you are trying to go the opposite of your original post. You have a string in that format and you want to convert it to an Oracle timestamp.

This should work, although messy. The mess comes from converting GMT to the local time zone. GMT didn't seem to be recognized by the TZD format element, so I used replace to change it to the offset for GMT, which is 0.

select cast(to_timestamp_tz(
replace(upper('Mon Aug 03 09:49:48 GMT 2009'), 'GMT','0:0'),
'DY MON DD HH24:MI:SS TZH:TZM YYYY') as timestamp with local time zone)
from dual;

1:
2:
3:
4:

Select all Open in new window

by: delmandiyarPosted on 2009-08-05 at 07:56:40ID: 25024155

thank you johnsone for your answar: your query is failing with ORA-01846!

Thank you all for good answares, the reason of i'm asking my qustion in this way is because i need to create a table wich have column that is a sysdate or systimestamp type. Then i want to insert this String (Mon Aug 03 09:49:48 GMT 2009) to the coulmn in away that Oracle can understand it.

MarkusId and Andytw both of your querys helped me alot.

by: johnsonePosted on 2009-08-05 at 08:04:39ID: 25024249

It works fine when I run it.

It is validating the day of the week, so if you ran this against your existing table, you may have invalid data.

SQL> select cast(to_timestamp_tz(
  2  replace(upper('Mon Aug 03 09:49:48 GMT 2009'), 'GMT','0:0'),
  3  'DY MON DD HH24:MI:SS TZH:TZM YYYY') as timestamp with local time zone)
  4  from dual;
 
CAST(TO_TIMESTAMP_TZ(REPLACE(UPPER('MONAUG0309:49:48GMT2009'),'GMT','0:0'),
---------------------------------------------------------------------------
03-AUG-09 05.49.48.000000 AM

1:
2:
3:
4:
5:
6:
7:
8:

Select all Open in new window

by: delmandiyarPosted on 2009-08-06 at 03:44:24ID: 25031808

Hey johnsone, i'm not runing that against my table, i run it just like it ism, and i'm getting Error ORA-01846 not a valid day of the week!

by: delmandiyarPosted on 2009-08-06 at 05:38:32ID: 25032513

what i need is to this string (Mon Aug 03 09:49:48 GMT 2009) and insert it to a column in a table wich is a type of timestamp.

by: johnsonePosted on 2009-08-06 at 05:41:44ID: 25032537

You really don't need the day of the week, so this should work as well.

select cast(to_timestamp_tz(
replace(substr(upper('Mon Aug 03 09:49:48 GMT 2009'), 4), 'GMT','0:0'),
'MON DD HH24:MI:SS TZH:TZM YYYY') as timestamp with local time zone)
from dual;

1:
2:
3:
4:

Select all Open in new window

by: MarkusIdPosted on 2009-08-06 at 06:18:52ID: 25032918

Hi,

So, I hope we get you right now:

You have a date which is currently stored in the database as
a characterfield as provided (Mon Aug 03 09:49:48 GMT 2009).
Now you want to take this column and store it as timestamp.

The remaining question is, do you want to save it keeping the
timezone or do you want to convert it to the current timezone?

As timestamp keeping timezone:
 
select FROM_TZ(
         CAST(
           to_date(
             substr('Mon Aug 03 09:49:48 GMT 2009', 5, 15) ||
               substr('Mon Aug 03 09:49:48 GMT 2009', 25, 4),
             'MON DD HH24:MI:SS YYYY')
           AS TIMESTAMP), 
         substr('Mon Aug 03 09:49:48 GMT 2009', 21, 3)
       )
  from dual
/
 
OR converted to local timezone:
 
select CAST(FROM_TZ(
         CAST(
           to_date(
             substr('Mon Aug 03 09:49:48 GMT 2009', 5, 15) ||
               substr('Mon Aug 03 09:49:48 GMT 2009', 25, 4),
             'MON DD HH24:MI:SS YYYY')
           AS TIMESTAMP), 
         substr('Mon Aug 03 09:49:48 GMT 2009', 21, 3)
       ) AS TIMESTAMP WITH LOCAL TIME ZONE)
  from dual
/

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:

Select all Open in new window

by: delmandiyarPosted on 2009-08-07 at 00:12:08ID: 31611916

Her is how i solved my problem:
insert into dato_test (dato) (select to_timestamp_tz(
replace(upper('Mon Aug 03 09:49:48 GMT 2009'), 'GMT','0:0'),
'DY MON DD HH24:MI:SS TZH:TZM YYYY') as GMT
from dual);

but insted of Mon i used Man to try it on mine computer, because my system is in Danish languge. And it was therfor johnsone's query was failling.

Question

How do i convert oracle sysdate with GMT

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

Oracle date formate

Oracle Database

SQL Query Syntax

PL / SQL

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Need individual assistance?

Our experts are ready to help.

Want to learn from the best?

Read articles from industry experts.

Working on a long term project?

Store your work and research.

What Makes Experts Exchange Unique?

Trusted by the world's most respected brands.

Faithfully serving IT professionals since 1996.

Related Solutions

Free Tech Articles

Cloud Class Webinars

Join the Community

Give a Little. Get a Lot.

Answers

3 Ways to Join

The Experts

The Experts

Testimonials

Testimonials

Testimonials

Business Clients

Business Clients

In the Press

In the Press

In the Press