DETECTDATE

Restriction: This topic applies to DETECTDATE as an HCOPG SQL compiler directive option only. For information on using the OpenESQL DETECTDATE SQL compiler directive option, see DETECTDATE under SQL Compiler Directive Options.

DETECTDATE enables you to specify alternative formats for input host variables. HCOPG manages the translation between the format specified by DETECTDATE and the format recognized by PostgreSQL. This is done for each SQL call on both input and output.

For example, PostgreSQL supports formatted values for datetime fields as cited in HCOPG Datetime Data Type Handling. For a mainframe DB2 application that uses datetime formats, such as EUR, that are not supported by PostgreSQL, with DETECTDATE you can still run it against a PostgreSQL database keeping the EUR datetime formats intact even though the PostgreSQL database does not accept those formats.

Important: Use DETECTDATE with extreme caution and only when absolutely necessary. Before using DETECTDATE, carefully review all options specified here, all of the information presented in HCOPG Datetime Data Type Handling, and the information in the DATE and TIME topics to help you determine the best fit for your application.

Syntax:

[NO]DETECTDATE
DETECTDATE={CLIENT | SQLTYPE | SERVER | PICX}

Parameters:

CLIENT

Applies to DBMAN=ODBC only.

Input Host Variables

For PIC X character input host variables, HCOPG recognizes specified datetime formats and translates the data into a format acceptable to PostgreSQL for processing. Specified datetime formats for input host variables are:

Date

Mainframe default, can be overridden by specifying the DATE HCOPG directive.

Time

Mainframe default, can be overridden by specifying the TIME HCOPG directive.

Timestamp

yyyy-mm-dd-hh.mm.ss.ffffff
yyyy-mm-dd hh.mm.ss.ffffff
yyyy-mm-dd hh:mm:ss.ffffff
yyyy-mm-ddThh.mm.ss.ffffff
yyyy-mm-ddThh:mm:ss.ffffff

Output Host Variables

For PIC X as well as SQLTYPE output character host variables, HCOPG returns the data for output host variables in the following datetime formats:

Date: Mainframe default, can be overridden by specifying the DATE HCOPG directive.
Time: Mainframe default, can be overridden by specifying the TIME HCOPG directive.
Timestamp: yyyy-mm-dd-hh.mm.ss.ffffff

SQLTYPE

Applies to DBMAN=ODBC only. Processing is identical to the CLIENT option, but applies only to SQLTYPE host variables and not PIC X.

SERVER

Applies to DBMAN=ODBC only.

Issues SQLDescribeParam calls to the DBMS to identify which input and output host variables are associated with specific character or datetime columns in the database. Host variables associated with datetime columns are translated with the datetime formats listed in CLIENT. Character columns are not translated.

PICX

Identical to the CLIENT option. Provided for backward compatibility.

Properties:

Defaults:

When DETECTDATE is not specified, the default is: NODETECTDATE

When DETECTDATE with no argument is specified, the default is: DETECTDATE=CLIENT

Scope:

Used at compile time:	Yes
Behavior at run time:	Source file

See Scope - HCOPG SQL Compiler Directive Options for more information.

Comments:

Use DETECTDATE=CLIENT only when your mainframe DB2 application does not use character columns in the database to store data that is a match to any of the alternative formats for PostgreSQL date, time, or timestamp fields.
Use DETECTDATE=SERVER when your mainframe DB2 application uses datetime values in both datetime and character columns of tables in the SQL server database.
Note: DETECTDATE=SERVER does cause HCOPG to perform extra overhead processing on each applicable SQL statement.
Datetime formats are defined implicitly unless you set them explicitly by specifying the DATE and/or TIME directives. See DATE and TIME for more information.