How do I cast a decimal field to a date field in DB2 AS/400?

Question

I have a DECIMAL field which contains a date in the AS400 format: 1100614

I have tried:

cast (MYDATE as DATE)

but I can't cast a DECIMAL to DATE, whereas

DATE(MYDATE)

returns null.

How do I cast this field in a DATE field?

Solution

Assuming it is CYYMMDD like I asked in my comment above, here are a couple options:

1. Convert CYYMMDD format to datetime - SQL
2. convert cyymmdd (AS400) format to datetime format

It can be done, but it isn't easy. If you need to do this often, it may be worth your time writing your own function to do this.