When date and time routines are taken

A date or time routine is invoked to change a value from the locally defined format to a format that is recognized by Db2.

A date or time routine is invoked in the following circumstances:

  • Begin program-specific programming interface information.When a date or time value is entered by an INSERT or UPDATE statement, or by the LOAD utility
  • When a constant or host variable is compared to a column with a data type of DATE, TIME, or TIMESTAMP
  • When the DATE or TIME scalar function is used with a string representation of a date or time in LOCAL format
  • When a date or time value is supplied for a limit of a partitioned index in a CREATE INDEX statement
The exit is taken before any edit or validation routine.
  • If the default is LOCAL, Db2 takes the exit immediately. If the exit routine does not recognize the data (EXPLRC1=8), Db2 then tries to interpret it as a date or time in one of the recognized formats (EUR, ISO JIS, or USA). Db2 rejects the data only if that interpretation also fails.
  • If the default is not LOCAL, Db2 first tries to interpret the data as a date or time in one of the recognized formats. If that interpretation fails, Db2 then takes the exit routine, if it exists.

Db2 checks that the value supplied by the exit routine represents a valid date or time in some recognized format, and then converts it into an internal format for storage or comparison. If the value is entered into a column that is a key column in an index, the index entry is also made in the internal format.

On retrieval, a date or time routine can be invoked to change a value from ISO to the locally-defined format when a date or time value is retrieved by a SELECT or FETCH statement. If LOCAL is the default, the routine is always invoked unless overridden by a precompiler option or by the CHAR function, as by specifying CHAR(HIREDATE, ISO); that specification always retrieves a date in ISO format. If LOCAL is not the default, the routine is invoked only when specifically called for by CHAR, as in CHAR(HIREDATE, LOCAL); that always retrieves a date in the format supplied by your date exit routine.

On retrieval, the exit is invoked after any edit routine or Db2 sort. A date or time routine is not invoked for a DELETE operation without a WHERE clause that deletes an entire table in a segmented table space. End program-specific programming interface information.