CODEWITHSUNDEEP
sqldb2
Geoff_S
Geoff_S

Reputation: 5105

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?

Upvotes: 1

Views: 231

Answers (3)

Charles
Charles

Reputation: 23793

rather than concatenating strings...

A better option might be to use DIGITS()

DATE(TIMESTAMP_FORMAT(DIGITS(decimalField) , 'MMDDYYYY'))

It's not that the zero isn't there, it's that the implicit conversion via CHAR() loses it.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270331

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.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521904

One option would be to right-pad the date decimal string with zero:

DATE(TIMESTAMP_FORMAT(
    RIGHT('0' || CHAR(decimalField), 8), 'MMDDYYYY'))

For a decimal string value such as 12122020, which has a two digit month, the above logic would operate on that same value. For values such as 5122019, we would left pad with zero first to obtain 05122019 before trying to convert to a date/timestamp.

All this being said, the best option here would probably be to store dates as date types, and not as decimals or string representations.

Upvotes: 1

Related Questions