Allows datetime values for PIC X character input host variables in a mainframe DB2 application to be in different formats than those supported by SQL Server.
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.
