I have several DB2 tables where the dates are defined as YYMD (usage=YYMD and actual=DATE). When I need to aggregate the data into a different format like MYY or YY for graphing, I get an error message stating that the field has an unsupported data type. Now I have tried converting the date by a define file two ways, field/myy=date_field and field/MYY=DATECVT(date_field,'YYMD','MYY') as well as played with computes and reformatting on the fly, but I get the same result each time.
Yes I do get a nice single select statement and my answer set does get returned, and I could hold the data and perform a second pass. however, I do not get to let the relational engine aggregate the data for me for faster processing. Done anyone know how to get a reformatted date to pass so aggregation can be done using it?

This message has been edited. Last edited by: j42p11,

DATE conversions are not very well supported by all iWay adapters and won't translate to your SQL engine. I know the iWay adapter for Oracle does nor offer great support on that but think the MS SQL Sesrver one does. Unfortunately I have no idea what your DB2 one supports but I'd dare to say that it does not support those conversion expressions to be translated to SQL and passed to the engine.

My advice: try and create some sort of Calendar dimension table in your database where, for each date you'll have additional attributes such as Month-Year, Year, etc. You can then join your existing tables to that Calendar and sort/group by the new attributes. No DATE conversions from FOCUS to DB are needed and all will be handled by the database directly.

Thanks njsden, I though this might be the case for me. I did find some documentation for new features on 7.7.05 that will allow the passing of native database functions through define fields that can solve my problem, if I were at that release level. So at least there is some light on the horizon. I will look into the date table like you suggested as a fix though. Thanks again.