It works if you use LPAD() so the string has 8 characters:

select t.*,
       DATE(TIMESTAMP_FORMAT(LPAD(decimalField, 8, '0') , 'MMDDYYYY'))
from (SELECT 5122019 as decimalField
      FROM sysibm.sysdummy1
     ) t

Here is a db<>fiddle.

You might want to be sure that the DD component is zero-padded. Otherwise, you cannot reliably make the conversion, because 1112020 could be either November 1st or Jan 11th.

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top