[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4005
  • Last Modified:

TO_DATE of a given long date string

Hi,

how can the following date-strings be converted to a date in Oracle SQL?

Thu Jul 26 09:18:51 GMT-0700 2007
Wed Jul 25 09:40:35 GMT+0200 2007

My code does not work:
TO_DATE('Thu Jul 26 09:18:51 GMT-0700 2007', 'DY MON DD HH24:MI:SS GMT TZHTZM YYYY')

The errormessage is: "The date format is not recognized"
0
TheFunkSoulBrother
Asked:
TheFunkSoulBrother
  • 3
  • 2
1 Solution
 
GGuzdziolCommented:
This is TIMESTAMP WITH TIME ZONE, DATE datatype is not aware of time zone. Hence TO_TIMESTAMP_TZ is suitable for You. Also remove GMT from format.

SQL> SELECT TO_DATE('Thu Jul 26 09:18:51 -0700 2007', 'DY MON DD HH24:MI:SS TZHTZM YYYY') FROM DUAL;
SELECT TO_DATE('Thu Jul 26 09:18:51 -0700 2007', 'DY MON DD HH24:MI:SS TZHTZM YYYY') FROM DUAL
                                                 *
ERROR at line 1:
ORA-01821: date format not recognized


SQL> SELECT TO_TIMESTAMP_TZ('Thu Jul 26 09:18:51 -0700 2007', 'DY MON DD HH24:MI:SS TZHTZM YYYY') FROM DUAL;

TO_TIMESTAMP_TZ('THUJUL2609:18:51-07002007','DYMONDDHH24:MI:SSTZHTZMYYYY')
---------------------------------------------------------------------------
26-JUL-07 09.18.51.000000000 AM -07:00
0
 
TheFunkSoulBrotherAuthor Commented:
Hi,

thank you very much, unfortunately it doesn't work.

SELECT TO_TIMESTAMP_TZ('Thu Jul 26 09:18:51 -0700 2007', 'DY MON DD HH24:MI:SS TZHTZM YYYY') FROM DUAL;
results in the error "invalid column name"

I guess, the problem is the database  - it's Oracle version 8.1.7.4.0

Is there any way to convert the string to a date/timestamp with this version of Oracle?
0
 
GGuzdziolCommented:
I'm afraid there is no TIMESTAMP WITH TIME ZONE datatype before 9x - http://asktom.oracle.com/pls/asktom/f?p=100:11:4100793985130956::::P11_QUESTION_ID:5011677391274
0
 
TheFunkSoulBrotherAuthor Commented:
:(

Ok, thank you anyway.
Maybe I'll have to split up the String using string operations and PL/SQL or so.
Regular expression aren't supported by Oracle 8 either, right?
0
 
GGuzdziolCommented:
Right - TIMESTAMP WITH TIME ZONE was introduced in 9, regular expressions - 10g. Probably substr/instr are Your option.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now