Change datatable (from AS400) column format

Question

I'm having this issue:

Our AS400 keeps dates as Decimal in format yyyyMMdd but when I need them in our software they should be DateTime in format ddMMyyy

How do I best go about getting the right format and type? I looked at IBM website for setting the Format at connection... But it seems you need to have your date in a DateTime type of column to start with to be able to set a format.

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Frzaik%2Frzaikconnstrkeywordsformatprop.htm

I also tried cloning the datatable and setting the appropriate columns to the correct datatype but then I need to loop all rows and each cell to convert which is next to impossible to do on large queries.

Can anybody help me out?

This is the code I used to change the datefields doing the query:

' convert date for selects
        If type = Operation.SelectMultipleRows Or type = Operation.SelectSingleValue Then

            For Each s As String In _datefields
                sql = sql.Replace(s, "REPLACE(CHAR(DATE(SUBSTR(CHAR(" & s & "), 1, 4) ||'-'|| SUBSTR(CHAR(" & s & "), 5, 2) ||'-'|| SUBSTR(CHAR(" & s & "), 7, 2)),EUR),'.','/') as " & s)
            Next

        End If

The above only works if requested fields are given in select query. If you use * then it simply does not work.

In iSeries AS400 you can get a list of all the column names if you know the tablename but I can't rely on it because the query passed could contain joins, ....

Example:

SELECT COLUMN_NAME FROM QSYS2/COLUMNS WHERE TABLE_NAME = 'MYTABLE'  

Answer 1

There is an actual date data type available on DB2 for IBM i. There are some older databases which still use a decimal number rather than a date data type, which is the situation you have.

If the IBM admin will let you load software onto the i, try Alan Campin's iDate service program. It will give you a set of user defined functions that will perform decimal to date conversions so that you can use fairly simple SQL statements to manipulate these numeric data types.

If you can't load software onto the IBM side, you will be forced to do the conversions yourself through some ugly variant of:

date(
     substr(char(numericDate),1,4) concat 
     '-' concat 
     substr (char(numericDate),5,2) concat 
     '-' concat 
     substr(char(numericDate),7,2)
  )

This contortion is needed because the DATE() function requires a character field in ISO format.

Answer 2

Request a reformatted date in your field selections:

date(substr(datefield,1,4) || '-' || substr(datefield,5,2)|| '-' || substr(datefield,7,2)) as datefield

Answer 3

If you can't download or use the SQL functions mentioned in @Buck's answer, or , I recommend that you (or someone) create an SQL User Defined Function in the database.

Make it a DETERMINISTIC function, which means that the results will the same whenever the input to the function is the same. This allows DB2 to cache the results, which will give you better performance, since it won't have to repeat the calculation each and every time it sees the same value again.

Perhaps something along the lines of this:

CREATE FUNCTION YMD2DATE (ymd dec(8,0) )
                        returns  date
       SPECIFIC YMD2DATE_dec8
       LANGUAGE SQL
       CONTAINS SQL
       DETERMINISTIC
       NO EXTERNAL ACTION
       RETURNS NULL ON NULL INPUT
       NOT FENCED
       SET OPTION DBGVIEW = *SOURCE
prc:       BEGIN NOT ATOMIC  -- not atomic means dont rollback on error
             DECLARE ans    date    default '0001-01-01';
             SET     ans = date( insert(insert( trim(digits(ymd)) ,7,1,'-'),5,1,'-'))  );
             RETURN  ans;
           END prc
;