Previous Topic Next topic Print topic


TIMESTAMP-RECORD SQL Type

Note: OpenESQL only

Syntax

SQL [TYPE] [IS] TIMESTAMP-RECORD

Use TIMESTAMP-RECORD to generate a group-level record for the timestamp containing individual records for each element of the timestamp as follows:

  • Year
  • Month
  • Day
  • Hour
  • Minute
  • Second
  • Fractional second

To insert data, you must pass valid data in the generated field names.

  • Data must be organized in fixed date/time formats.
  • Fractional seconds are supported up to nine digits. However, this value can vary depending on your target DBMS and your ODBC driver. For example, DB2 ECM limits fractional seconds to six digits. Microsoft SQL Server limits fractional seconds to three digits. See your DBMS or ODBC driver documentation for more information.
  • Fractional data is passed left justified, and must contain the number of digits defined in your record. For example, to pass a fractional value of 678 to a record that defines fractional data as nine digits, move the value 678000000.
  • Fractional data is right justified when returned from a SELECT or FETCH statement.
  • Because of the way SQL Server stores date/time values, it might round the last fractional digit of a fractional value up or down depending on the digit. For example:
    If you pass... SQL Server returns...
    01/01/98 23:59.59.999 1998-01-02 00:00:00.000
    01/01/98 23:59.59.995 1998-01-01 23:59:59.997
    01/01/98 23:59.59.996 1998-01-01 23:59:59.997
    01/01/98 23:59.59.997 1998-01-01 23:59:59.997
    01/01/98 23:59.59.998 1998-01-01 23:59:59.997
    01/01/98 23:59.59.992 1998-01-01 23:59:59.993
    01/01/98 23:59.59.993 1998-01-01 23:59:59.993
    01/01/98 23:59.59.994 1998-01-01 23:59:59.993
    01/01/98 23:59.59.990 1998-01-01 23:59:59.990
    01/01/98 23:59.59.991 1998-01-01 23:59:59.990

Example

01 hv-name SQL TYPE IS TIMESTAMP-RECORD.

generates

01 hv-name.
  03 hv-name-year  PIC S9(4) COMP-5.
  03 hv-name-month PIC 9(4) COMP-5.
  03 hv-name-day   PIC 9(4) COMP-5.
  03 hv-name-hour  PIC 9(4) COMP-5.
  03 hv-name-min   PIC 9(4) COMP-5.
  03 hv-name-sec   PIC 9(4) COMP-5.
  03 hv-name-frac  PIC 9(9) COMP-5.
Previous Topic Next topic Print topic