Handling of Timestamp or DateTime columns
These notes apply to the datatypes recognised 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 loaded into the LANSA Data Dictionary as a field 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 field 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 field's default value where the field length is 26 or higher. If you want to use these values with a shorter field, use a virtual field of length 26.
If the field 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 field was set to when added to the Data Dictionary.
After a fetch from the file, the field 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 field is inserted/updated, the time portion will be automatically set to 00:00:00
- Time only (HH:MM:SS.[f...])
If the field 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 field 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 fields to convert to and from the above formats.
Also See
Using SQL Server datatype Smalldatetime