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|
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.
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:
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:
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 (:).
|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.1|
|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.2|
|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|
|1We recommend that you use DETECTDATE when also using TIME values with Oracle.
2Optionally, you can use alternative datetime SQL compiler directive options.
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|