DETECTDATE

Restriction: This topic applies to DETECTDATE as an HCOSS 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. HCOSS manages the translation between the format specified by DETECTDATE and the format recognized by SQL Server. This is done for each SQL call on both input and output.

For example, SQL Server supports formatted values for datetime fields as cited in HCOSS Datetime Data Type Handling. For a mainframe DB2 application that uses datetime formats, such as EUR, that are not supported by SQL Server, with DETECTDATE you can still run it against a SQL Server database keeping the EUR datetime formats intact even though the SQL Server 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 HCOSS 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 or DBMAN=ADO.

Input Host Variables

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

Date

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

Time

Mainframe default, can be overridden by specifying the TIME HCOSS 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, HCOSS returns the data for output host variables in the following datetime formats:

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

SQLTYPE

Note: Deprecated feature starting with Enterprise Developer version 2.3, and provided for backward compatibility only. Datetime processing of SQLTYPE host variables is now independent of DETECTDATE.

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

SERVER

Applies to DBMAN=ODBC, or DBMAN=ADO for SQL Server version 2012 and later.

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 - HCOSS 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 SQL Server date, time or datetime2 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 HCOSS 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.