ORA-01840: input value not long enough for date format SQL Oracle

Question

Im trying to update table using code below but got error SQL Error: ORA-01840: input value not long enough for date format, DUE_ON_DT_WID is a number column with sample records like this '20191231' and the expected sult on X_NEED_BY_DATE is '31-DEC-19'. X_NEED_BY_DT is a date column. Thank you in advance for the help.

update      ADW12_DW.W_PURCH_COST_F T
set     (
    T.X_NEED_BY_DT
    ) =
        (
        select  
        TO_DATE(DUE_ON_DT_WID,'YYYYMMDD')
        from    ADW12_DW.I$_1200778522_6 S
        where   T.DATASOURCE_NUM_ID =S.DATASOURCE_NUM_ID
        and T.INTEGRATION_ID    =S.INTEGRATION_ID
             )

where   (DATASOURCE_NUM_ID, INTEGRATION_ID)
    in  (
        select  DATASOURCE_NUM_ID,
            INTEGRATION_ID
        from    ADW12_DW.I$_1200778522_6
        where   IND_UPDATE = 'U'
        )

Answer 1

There must be an issue with your data as format YYYYMMDD must have 8 as the length of data (as Year is coming first in your data).

Please find the issue data using the following query and correct it.

select * from ADW12_DW.I$_1200778522_6 where length(DUE_ON_DT_WID) <> 8

I think you have the data like this 20191231 and also 191231. do you want to consider them as the same? i.e. '31-DEC-19' then you need to use the CASE..WHEN and RR/YYYY for formatting the Year as follows:

CASE WHEN LENGTH(DUE_ON_DT_WID) = 6 THEN 
       TO_DATE(DUE_ON_DT_WID,'RRMMDD')
     WHEN LENGTH(DUE_ON_DT_WID) = 8 THEN
       TO_DATE(DUE_ON_DT_WID,'YYYYMMDD')
END

If you are using Oracle 12.2 or higher then you can use ON CONVERSION ERROR clause in the TO_DATE to default some value when the conversion from your column to date fails as follows:

TO_DATE(DUE_ON_DT_WID DEFAULT '20010101' ON CONVERSION ERROR, 'YYYYMMDD' )