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.
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'