Question 197723 in PostgreSQL Question posted in
The official documentation can be found here.

ORA-01830: incorrect date with timestamp from Postgresql to Oracle format

I have the following Timestamp With Time Zone column value in Postgresql:

2011-09-13 07:38:01+00

Which I convert to the necessary Date format expected by Oracle in the select:

select to_date(date_column, 'DD.MM.YYYY HH24.MM.SS') as date_column from table;

Which matches the Date format expected by Oracle or NLS mask. Upon inserting the row into Oracle through the ETL process however, this error is thrown:

ORA-01830: date format picture ends before converting entire input string

Any ideas what may fix the conversion correctly?

Tags:

2

Answers


  1. 0

    If I assume date_column is holding a value 2011-09-13 07:38:01+00 and thus its datatype would be VARCHAR2. In such case, you can first get zone +00 trimmed using SUBSTR, and then applying ‘to_date’ with a compatible format should give a date.

    select to_date(SUBSTR(date_column, 1, 19), 'YYYY-MM-DD HH24:MM:SS') as date_column from table;

    Login or Signup to reply.
  2. 0

    In this query

    select to_date(date_column, 'DD.MM.YYYY HH24.MM.SS') as date_column from table;

    You have "MM" for MONTH and MINUTES both, try switching to acceptable format. As per my knowledge "MI" is used for minutes. This might help.

    SELECT TO_DATE(date_column, 'DD.MM.YYYY HH24:MI:SS') AS date_column FROM table;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search