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) : can't cast DECIMAL to DATE, DATE(MYDATE) returns null.

How do I cast this field in a DATE field ?

Answer 1

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.

Answer 2

At a recent DB2 for i class I attended, IBM folks were suggesting a pre-populated conversion table, that contains the date values in all the different formats you need to support. You only need 365 or 366 rows per year. You then join to this table to convert. Of course you'll need to create the indexes for each format to have good performance. One guy stated the performing the join could even be faster than calling a conversion function.

Another advantage is that you can add other columns in the conversion table such as the fiscal period, the quarter, business day indicator, etc, which can simplify a bunch of queries, such as finding all orders in the third quarter.

For instance, say the DATECONV table has columns DATEFMT as a real date and CYMDFMT as the decimal date, your select would be SELECT DATEFMT, ... FROM MYTABLE JOIN DATECONV on CYMDFMT=MYDATE

One issue with this solution is if you use invalid dates as special values such as 9999999 as you can't ave invalid dates in the real date field. But then you can't convert them any way.

Answer 3

This will work if you're on IBM i release 6.1 or newer, but I have a hunch you're on an old version of the operating system. I stole the adding trick from this forum post.

create table dec1 (f1 decimal(7,0))
insert into dec1 values 1100614

select date(timestamp_format(char(f1+19000000), 'YYYYMMDD')) from dec1

Results:

DATE                           
06/14/10                       
********  End of data  ********

Here's the reference to the supported date string formats in DB2 for i SQL.