Getting a date format error while executing-CodePudding

ORA-01840: input value not long enough for date format 01840. 00000 - "input value not long enough for date format" *Cause:
*Action:

SELECT TO_DATE (
          TO_CHAR (TO_DATE (attribute39, 'MM/DD/YYYY'), 'DD/MM/YYYY'),
          'DD/MM/YYYY') AS "PO Valid To Date"
  FROM table;

Want to execute the query without error, attribute39 is date formate in mm/dd/yyyy and varchar(250)

CodePudding user response：

TO_DATE supports 'DEFAULT return_value ON CONVERSION ERROR' and if you prefix the date format with 'FX' you will be able to detect rows where attribute39 is not exactly compliant to your expectation:

TO_DATE (attribute39, DEFAULT to_date('01/01/0001','dd/mm/yyyy') ON CONVERSION ERROR, 'FXMM/DD/YYYY')

You could put NULL as DEFAULT if you don't have it as possible value for attribute39, if not selecting a value you are sure is not in your data makes easier to detect rows with invalid attribute39.

You may get ORA-01840 if you have strings where the year is only two digits (meaning from 1950 to 2049).

You could also run a query with a regex to detect unexpected values in attribute39.

CodePudding user response：

Yes used the regex expression to solve the issue it is working found the different format updated the same with ri8 one able to execute now.