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.