Created On:  17 May 2012

Problem:

Application was originally developed as native code using Oracle's Pro*COBOL and Net Express using the COBSQL directive.

The following statement was used to change the format of DATE columns so that they would be returned in MM-DD-YYYY format.

EXEC SQL ALTER SESSION SET NLS_DATE_FORMAT = 'MM-DD-YYYY' END-EXEC.

This worked correctly under Net Express and Pro*COBOL.  After converting the application to Visual COBOL managed .NET code using the OpenESQL preprocessor the date columns are no longer being returned in the format specified in the set NLS_DATE_FORMAT statement.

They are instead being returned as YYYY-MM-DD format.

Resolution:

This is a compatibility issue with DATE formats between Pro*COBOL and OpenESQL.

In OpenESQL columns defined as DATE will always be returned in YYYY-MM-DD format regardless of the setting of NLS_DATE_FORMAT.

To get around this you must use the TO_CHAR function on the DATE column so that the data will be returned as character data instead of converted to the OpenESQL DATE format.

Example:

Pro*COBOL statement:

    EXEC SQL
           SELECT CURRENT-DATE INTO :WS-THEDATE
           FROM DUAL
    END-EXEC

OpenESQL statement:

 EXEC SQL
       SELECT TO_CHAR(CURRENT-DATE) INTO :WS-THEDATE
       FROM DUAL
 END-EXEC





           EXEC SQL
             SELECT TO_CHAR(CURRENT_DATE) INTO :WS-THEDATE
             FROM DUAL
           END-EXEC.
Incident #2574103