Handling of Timestamp or DateTime columns

These notes apply to the datatypes recognized by ODBC as SQL_TIMESTAMP, as well as the IBM i native datatype Timestamp (Z). These datatypes contain a date and time in one column.

When a timestamp column is imported into the LANSA Data Dictionary as a column without the ASQN attribute, the default value '1900-01-01' is assigned. This value must be reviewed to verify its suitability.

The length of the column is between 19 and 29, depending on the native datatype.

The system variables *TIMESTAMP_DFT, *TIMESTAMP_LOVAL, and *TIMESTAMP_HIVAL are also available. On platforms other than IBM i, *TIMESTAMP_DFT and *TIMESTAMP_LOVAL both evaluate to '1900-01-01 00:00:00.000000'. On IBM i, these system variables evaluation to '0001-01-01.00.00.00.000000'. On both platforms, *TIMESTAMP_HIVAL evaluates to midnight on 9999-12-31.

The *TIMESTAMP_XXX variables may only be specified as the column's default value where the column length is 26 or higher. If you want to use these values with a shorter column, use a virtual column of length 26.

If the column is of type Alphanumeric, one of the following formats must be used:

• Full timestamp (YYYY-MM-DD HH:MM:SS[.f....]
  Seconds must always be specified, and the fractional part may only be specified where the native datatype supports it, and only for the maximum length the column was set to when added to the Data Dictionary.
  
  After a fetch from the table, the column will always be in this format, unless it is NULL, in which case it will be blanks. If the row is then updated, LANSA will automatically set the column to *TIMESTAMP_DFT as blank is not valid.

• Date only (YYYY-MM-DD)
  If the column is inserted/updated, the time portion will be automatically set to 00:00:00

• Time only (HH:MM:SS.[f...])
  If the column is inserted, the date portion will be automatically set to '1900-01-01'. The same format rules apply as for the full timestamp. Do not specify a fractional part if it cannot be specified in the full timestamp format.

• If the column is used in an I/O command and is not in a valid format, a fatal error will occur. On insert or update some datatypes may be successfully converted  by the DBMS from other formats, although this is not recommended. If data may be manually entered, you should use virtual columns to convert to and from the above formats.

Also See

Using SQL Server datatype Smalldatetime

IBM i RDMLX Other Tables