TheFunkSoulBrother
asked on
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"
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"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
:(
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?
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?
Right - TIMESTAMP WITH TIME ZONE was introduced in 9, regular expressions - 10g. Probably substr/instr are Your option.
ASKER
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?