TIMESTAMP

Data Format

Default and alternative timestamp value formats vary. If using DB2 ECM, consult IBM documentation on differing formats. For OpenESQL, review the TSTAMPSEP SQL compiler directive option topic for more information.

For example, one supported time format is:

yyyy-mm-dd hh:mm:ss[.f[f[...]]]

where the number of fractional digits is driver-defined. An example value for this format is:

1994-05-24 12:34:00.000

Host Variable Formats

OpenESQL and DB2 ECM

01 timestamp1         PIC X(29).
01 timestamp2         SQL TYPE IS TIMESTAMP.

The timestamp2 format uses the TIMESTAMP SQL TYPE.

OpenESQL

01 timestamp3         PIC X(n).
01 timestamp4         SQL TYPE IS TIMESTAMP-RECORD.

timestamp1 format

Move timestamp data into a host variable using the form:
```
MOVE "yyyy-mm-dd hh:mm:ss" TO timestamp1
```
Review the DETECTDATE SQL compiler directive option topic to determine whether or not it applies to your application.

timestamp2

Move timestamp data into a host variable using the form:
```
MOVE "yyyy-mm-dd hh:mm:ss" TO timestamp2
```
Preferred format - use whenever possible.
Uses the TIMESTAMP SQL TYPE.
Similar to the timestamp1 format, but when using Enterprise Developer version 2.3 or later, never requires DETECTDATE for input host variable processing.

timestamp3 format

Use this format when your application moves timestamp data to host variables using ODBC escape sequences.

Note: This format is deprecated starting with Enterprise Developer version 2.3. Instead, we strongly suggest that you use the TSTAMPDEP SQL compiler directive option to avoid using ODBC escape sequences.

Move timestamp data into a host variable using the form:

MOVE "{ts 'yyyy-mm-dd hh:mm:ss[.f[f[...]]]'}"
     TO timestamp3

Compile applications with the DETECTDATE SQL compiler directive option to ensure that OpenESQL evaluates PIC X fields for possible timestamp formats in input host variables.
Ensure n is large enough to accommodate the timestamp value plus the characters used by the ODBC escape sequence.
Output host variable values are returned without the ODBC escape sequence.

timestamp4 format

Move timestamp data into host variables using these forms:

MOVE yyyy TO host-var-year
MOVE mm TO host-var-month
MOVE dd TO host-var-day
MOVE hh TO host-var-hour
MOVE mm TO host-var-minute
MOVE ss TO host-var-sec
MOVE ff TO host-varc-fra

Uses the TIMESTAMP-RECORD SQL TYPE.

OpenESQL JVM Managed Runtime

In addition to the definitions valid for all databases and those valid for OpenESQL, the following definition is also valid for the OpenESQL JVM Managed Runtime:

01 timestamp5         type java.sql.Timestamp.

DB2 ECM

The TIMESTAMP data type has a maximum length of 26 characters.