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.