0

I'm trying to convert a VARCHAR2 variable which contains a timestamp w/ time zone into a DATE variable. I can convert the timestamp without the time zone but when I add logic for timezone I get "ORA-01821: date format not recognized". Please see code below:

    DECLARE
      v_string VARCHAR2(400) := '2011-05-12 19:04:41.032645 +00:00';
      v_date   DATE;
    BEGIN

      SELECT to_timestamp(v_string,'YYYY-MM-DD HH24:MI:SSxFF TZH:TZM')
        INTO v_date
        FROM dual;

    END;
user2212306
  • 1
  • 1

1 Answers1

2

Use TO_TIMESTAMP_TZ:

SQL> DECLARE
  2     v_string VARCHAR2(400) := '2011-05-12 19:04:41.032645 +00:00';
  3     v_date   DATE;
  4  BEGIN
  5     v_date := to_timestamp_tz(v_string, 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM');
  6  END;
  7  /

PL/SQL procedure successfully completed
Vincent Malgrat
  • 66,725
  • 9
  • 119
  • 171