TIMESTAMP and TIMESTAMP-RECORD

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

Syntax: for Format 1 - TIMESTAMP

SQL [TYPE] [IS] TIMESTAMP

Syntax: for Format 2 - TIMESTAMP-RECORD

SQL [TYPE] [IS] TIMESTAMP-RECORD

General Rules:

  1. TIMESTAMP data must be in the format YYYY-MM-DD HH:MM:SS.
  2. To insert data using a TIMESTAMP-RECORD host variable, you must pass valid data in the generated field names.
  3. Fractional seconds are supported up to nine digits. However, this value can vary depending on your target DBMS and your ODBC driver. For example, Oracle and DB2 UDB limit fractional seconds to six digits. See your DBMS or ODBC driver documentation for more information.
  4. 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.
  5. Fractional data is right justified when returned from a SELECT or FETCH statement.
  6. When using Oracle, the accepted format of date/time strings can vary depending on the locale and territory, which are defined by Oracle's NLS_TIMESTAMP_FORMAT parameter. This means that the format defined in OpenESQL must match the value specified for NLS_TIMESTAMP_FORMAT.
    Note:

    If the OpenESQL format does not match the value of Oracle's NLS_TIMESTAMP_FORMAT parameter, Oracle generates an error message. Use Oracle's SQLPLUS utility to determine the value of NLS_TIMESTAMP_FORMAT. Then do one of the following:

    • Set an environment variable, NLS_TIMESTAMP_FORMAT, to the value defined for OpenESQL.
    • Code a PIC X(29) character host variable and pass the value as a string in the format defined for NLS_TIMESTAMP_FORMAT.
    • Use the TO_TIMESTAMP function on the INSERT statement to specify the appropriate format. For example:
      exec sql
         insert into mf_datetime
           (col_a
           ,col_date
           ,col_timestamp
           ) values
          (:mf-col-a
          ,TO_DATE(:mf-col-date, 'YYYY-MM-DD')
          ,TO_TIMESTAMP(:mf-col-timestamp, 'YYYY-MM-DD HH24:MI.SS.FF')
          )
      end-exec

    See your Oracle documentation for information on the SQLPLUS utility and the NLS_TIMESTAMP_FORMAT parameter.

  7. 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

Comments:

When using any DBMS that uses more than three digits to represent fractional seconds, including Oracle and DB2 UDB (depending on the type of DB2 server used), be aware of the following when porting to .NET:

.NET limits fractional seconds of date/time objects to three digits. When using TIMESTAMP or TIMESTAMP-RECORD SQL TYPE host variables in your application, OpenESQL automatically returns the timestamp value with zeros in the last 3 digits. For example, a fractional value of 123456 under Windows is returned by .NET as 123000.

To get around this, code character host variables (i.e., PIC X(n)for your TIMESTAMP and TIMESTAMP-RECORD fields, and be sure the column defined in your EXEC SQL statement has been changed to character data using the TO_CHAR and TO_TIMESTAMP functions. For example:

  exec sql
       select
         a.col_date
        ,a.col_timestamp
        ,TO_CHAR(a.col_timestamp,'YYYY-MM-DD HH24:MI:SS.FF')
      into
        :mf-col-date
       ,:mf-col-timestamp
       ,:ws-char-ts
    from mf_datetime a
      where ( a.col_a = :mf-col-a )
  end-exec

To get the correct number of fractional digits returned to your application, compile your program with SQL directive TARGETDB. For example, to compile an Oracle application, specify the directives SQL(DBMAN=ADO TARGETDB=ORACLE)

See the TIMESTAMP demos for more information.

Example for Format 1 - TIMESTAMP

01 hv-name SQL TYPE IS TIMESTAMP

generates

01 hv-name pic x(29).

Example for Format 2 - TIMESTAMP RECORD

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.

Example for Formats 1 and 2

This host variable definition:

01  mf.
    03 mf-col-a                       pic s9(09)  comp-5.
    03 mf-col-date                    sql type date.
    03 mf-col-timestamp               sql type timestamp.
    03 mf-col-tsrec                   sql type timestamp-record.
01  ws-char-ts                        pic x(29).

Requires this field initialization:

move 1                 to        mf-col-a
move "2005-03-31"      to        mf-col-date
move "2005-04-15 13:45:56.456123"
     to        mf-col-timestamp
move 2005              to        mf-col-tsrec-year
move 04                to        mf-col-tsrec-month
move 16                to        mf-col-tsrec-day
move 16                to        mf-col-tsrec-hour
move 55                to        mf-col-tsrec-min
move 58                to        mf-col-tsrec-sec
move 678000000         to        mf-col-tsrec-frac

And this INSERT statement:

   exec sql
     insert into mf_datetime
       (col_a
       ,col_date
       ,col_timestamp
       ) values
       (:mf-col-a
       ,:mf-col-date
       ,:mf-col-timestamp
       )
  end-exec