DB2 Iseries 4 Issue casting date from decimal

I have decimal fields (DECIMAL(8,0)) that contain dates as 5122019 for may 12th 2019 and 12122020 as December 12th 2020.

I’ve tried several ways to convert including DATE(TIMESTAMP_FORMAT(CHAR(decimalField),'MMDDYYYY'))

but they return null. What’s the best way to do this with a decimal 8,0 field when the single digit months don’t contain a leading zero?

Answer

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.