Pass through Query to iSeries - Date problem

[domtrump]

I have a pass through query going to an iSeries (AS/400) with the following syntax:

SELECT
DATE( PRDOHH, HYF ) AS "DatePrdohh" 
FROM 
    HRDBFA.PRPMS PRPMS

This is a valid query on the host, but access returns the error:

Microsoft Access
ODBC--call failed.

[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0206 - Column or global variable HYF not found. (#-206)

The HYF is part of the date conversion function, not a column). Is there some way (via syntax) to force Access to let the host process this? Thanks.

 

[james_lankford]

is it HYF or HYD ?

 

[domtrump]

is it HYF or HYD ?

Yes, it is HYF (Hundred Year Format) in iSeries queries.

It was my understanding that a pass through query would just send the syntax to the host and process it there (though I could be wrong). It seems like Access is somehow parsing this differently causing the host to think this is a column and not part of a function. I'm wondering if there is some other way to write it using parentheses or brackets or something that will force Access to send it as a function? I'm not real clear on how pass through queries work, but I know this query works on the system itself.

Thanks for any help.

 

[james_lankford]

did a google
and found this
DB2 Universal Database for iSeries SQL Reference
https://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/db2/rbafzmstch2func.htm

it doesn't look like DATE has a format part

CHAR does have a date format specifier, but I don't see HYF
all I see is
ISO
USA
EUR
JIS

someone on this site
Convert serial number date to format mm/dd/yyyy in DB2 - IBM: DB2 - Tek-Tips

recommended this, which I think you can change around

SELECT DATE(DAYS('1900-01-01') - 1 + yourdatecolumninhere)
FROM yourtablenamehere

 

[domtrump]

Upon further research, it appears that the DATE([field],HYF) function is something that is specific to the ODBC driver that I normally use (IBM Showcase). Therefore, it doesn't work when the Access query tries to connect using the native iSeries ODBC driver. Your trick for using the DAYS function works just as good, though. Thanks for the assistance!