DETECTDATE

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.