Allows datetime values for PIC X character input host variables in an OpenESQL application to be in different formats than the standard ISO 8601 formats.
DETECTDATE enables you to specify alternative formats for input host variables. OpenESQL manages the translation between the format specified by DETECTDATE and the format recognized by your DBMS. This is done for each SQL call on both input and output.
For example, instead of the standard ISO formats, you might prefer to use EUR formats for date and time data.
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
OpenESQL Datetime Data Types Handling, and the information in the
DATE,
DATEDELIM,
TIME, and
TIMEDELIM 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, OpenESQL recognizes specified datetime formats and translates the data into the ISO 8601 format acceptable to your DBMS. Specified datetime formats for input host variables are:
- Date
- ISO 8601 default, can be overridden by specifying the DATE directive and/or the DATEDELIM directive.
- Time
- ISO 8601 default, can be overridden by specifying the TIME directive and/or the TIMEDELIM directive.
- Datetime
- ISO 8601 default, can be overridden by specifying the TSTAMPSEP directive.
The dash character instructs OpenESQL to look for a specific set of delimiters, including a dash, a space, and a T. For example if you do not specify any alternative date or time formats, and you set TSTAMPSEP to a dash character (-), OpenESQL recognizes the following formats in your input host variables:
- 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
All other characters instruct OpenESQL to search for that specific character between each date and time format, where the date portion is delimited by a dash character (-) and the time portion is delimited by a colon (:).
- Output Host Variables
- For PIC X output character host variables, OpenESQL returns the data for output host variables in the following datetime formats:
- Date
- ISO 8601 default, can be overridden by specifying the DATE directive and/or the DATEDELIM directive.
- Time
- ISO 8601 default, can be overridden by specifying the TIME directive and/or the TIMEDELIM directive.
- Timestamp
- ISO 8601 default, can be overridden by specifying the TSTAMPSEP directive. When TSTAMPSEP is set to a dash (-) character (TSTAMPSEP="-"), OpenESQL returns datetime columns in the following format:
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 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 - OpenESQL Compiler Directive Options for more information.
Comments:
- Use DETECTDATE=CLIENT only when your OpenESQL application does not use character columns in the database to store data that is a match to any of the alternative formats for the date, time or datetime fields in your DBMS.
- Use DETECTDATE=SERVER when your OpenESQL application uses datetime values in both datetime and character columns of tables in your database.
Note: DETECTDATE=SERVER does cause OpenESQL to perform extra overhead processing on each applicable SQL statement.
- Datetime formats are defined implicitly unless you set them explicitly by specifying the DATE, DATEDELIM, TIME, and/or TIMEDELIM directives. See
DATE,
TIME,
DATEDELIM, and
TIMEDELIM for more information.
