OpenESQL Datetime Data Type Handling

By default, OpenESQL supports ODBC/ISO 8601 formats for all input and output character host variables associated with datetime columns in your DBMS.

For example, when using a SQL Server DBMS, the default data type formats for character host variables are:

SQL Server Data Type	ODBC/ISO 8601 Format
date	`yyyy-mm-dd`
time	`hh:mm:ss`
datetime2	`yyyy-mm-dd hh:mm:ss.ffffffff`

In addition to SQL Server, these formats generally apply to other DBMS vendors that accept ISO 8601 formats. OpenESQL also supports alternative formats for both input and output character host variables. We provide several SQL compiler directive options that enable you to specify alternative formats that override the default.

Note: For Oracle databases, when using DBMAN=ADO, you can enable the NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT Oracle environment variables independent of other OpenESQL datetime processing directives by also specifying DATE=EXTERNAL. See the DATE SQL compiler directive option topic for details.

Input Host Variables - DETECTDATE

The DETECTDATE SQL compiler option directive instructs OpenESQL to examine the contents of PIC X character input host variables, looking for data that matches the default ISO 8601 formats. You can override the default formats by specifying one or more additional directives:

Note: For complete information on each directive, see its corresponding topic under SQL Compiler Directive Options.

DATE

Specify an alternative DATE format.

DATEDELIM

Specify an alternative delimiter for date columns.

When used with DATE, the alternative delimiter is applied to the alternative date format specified.
When used without DATE, the alternative delimiter is applied to the ISO 8601 date format.

TIME

Specify an alternative TIME format.

TIMEDELIM

Specify an alternative delimiter for time columns.

When used with TIME, the alternative delimiter is applied to the alternative time format specified.
When used without TIME, the alternative delimiter is applied to the ISO 8601 time format.

TSTAMPSEP

Specify a one-character delimiter used between the date and time portions of your input host variables.

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 (:).

If you do not specify TSTAMPSEP, OpenESQL defaults to searching for a space character as the delimiter between the date and time formats, where the date portion is delimited by a dash character (-) and the time portion is delimited by a colon (:).

Guidelines for using DETECTDATE

Use of the DETECTDATE directive can create significant processing overhead. To minimize this, we recommend that you follow the guidelines presented in the following usage scenarios:

Scenario	DETECTDATE option
My application uses date, time and datetimes values in PIC X input host variables, but I am happy with the supported ODBC/ISO 8601 formats, and have no use for alternative formats.	Not required.¹
My application uses date, time and datetime values in PIC X input host variables, but I only use those values in date, time, or datetime columns in my database.	CLIENT
My application uses ODBC escape sequences for date, time, and datetimes values in PIC X input host variables, but I only use those values in date, time, or datetime columns in my database.	CLIENT
My application uses date, time, and datetime values in PIC X input host variables, but I only use those values in character columns in my database. Also, my SQL does not use either implicit or explicit characters for date, time, or datetime2 data types.	Not required. Do not use DETECTDATE, DATE, or TIME SQL compiler directive options.
I only use SQLTYPE host variables with date, time and datetime columns, and never use PIC X host variables with date, time or datetime columns.	Not required.²
My application uses date, time and datetime values in PIC X input host variables, and I use those values both in character columns and in date, time and datetime columns in my database , and my character columns might use data in formats that could be confused with the formats for date, time or datetime values.	SERVER
¹We recommend that you use DETECTDATE when also using TIME values with Oracle. ²Optionally, you can use alternative datetime SQL compiler directive options.

Note:

We recommend that for SQL Server applications developed using Enterprise Developer version 2.2 Update 2 or earlier, you specify the DETECTDATE directive if the application uses PIC X host variables with datetime columns in your SQL Server database.
For complete information on all DETECTDATE options, see the DETECTDATE SQL compiler directive option topic.

Output Host Variables

By default OpenESQL returns date, time and datetime data types in the ISO 8601 default format. You can override the default format by specifying additional OpenESQL directives as follows:

DBMS Data Type	ODBC/ISO 8601 Format	OpenESQL Directives
date	`yyyy-mm-dd`	DATE, DATEDELIM
time	`hh:mm:ss`	TIME, TIMEDELIM
datetime	`yyyy-mm-dd hh:mm:ss.ffffff`	TSTAMPSEP