TIMESTAMP and 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