Datetime assignments

A value assigned to a DATE column or a DATE variable must be a date or timestamp or a valid string representation of a date or timestamp. When a timestamp value is assigned to a DATE data type, the date portion is extracted and the time portion is ignored. A date can be assigned only to a DATE or TIMESTAMP column, a string column, a DATE or TIMESTAMP variable, or a string variable.

A value assigned to a TIME column or a TIME variable must be a time or timestamp or a valid string representation of a time or timestamp. When a timestamp value is assigned to a TIME data type, the date portion is ignored and the time portion is extracted with the fractional seconds truncated. A time can be assigned only to a TIME column, a string column, a TIME variable, or a string variable.

A value assigned to a TIMESTAMP column or a TIMESTAMP variable must be a date or timestamp or a valid string representation of a date or timestamp. If a string representation of a date or timestamp is used, it is implicitly cast to a timestamp with the same precision as the target timestamp. When a date value is assigned to a TIMESTAMP data type, the missing time information is assumed to be all zeros. A timestamp can be assigned to a DATE, TIME, or TIMESTAMP column, a string column, a DATE, TIME, or TIMESTAMP variable, or a string variable. When a timestamp value is assigned to a timestamp with lower precision, the excess fractional seconds are truncated. When a timestamp value is assigned to a timestamp with higher precision, missing digits are assumed to be zeros.

When a datetime value is assigned to a string variable or column, it is converted to its string representation. Leading zeros are not omitted from any part of the date, time, or timestamp. The required length of the target varies depending on the format of the string representation. If the length of the target is greater than required, it is padded on the right with blanks. If the length of the target is less than required, the result depends on the type of datetime value involved and on the type of target.

• If the target is a string column, truncation is not allowed. The following rules apply:

  DATE

  • The length attribute of the column must be at least 10 if the date format is *ISO, USA, *EUR, or *JIS. If the date format is *YMD, *MDY, or *DMY, the length attribute of the column must be at least 8. If the date format is *JUL, the length of the variable must be at least 6.

  TIME

  • The length attribute of the column must be at least 8.

  TIMESTAMP

  • The length attribute of the column must be at least 19 for TIMESTAMP(0) and 20+p for TIMESTAMP(p).
• When the target is a variable, the following rules apply:

  DATE

  • The length of the variable must be at least 10 if the date format is *ISO, *USA, *EUR, or *JIS. If the date format is *YMD, *MDY, or *DMY, the length of the variable must be at least 8. If the date format is *JUL, the length of the variable must be at least 6.

  TIME

  • If the *USA format is used, the length of the variable must not be less than 8. This format does not include seconds.
  • If the *ISO, *EUR, *JIS, or *HMS time format is used, the length of the variable must not be less than 5. If the length is 5, 6, or 7, the seconds part of the time is omitted from the result, and SQLWARN1 is set to 'W'. In this case, the seconds part of the time is assigned to the indicator variable if one is provided, and, if the length is 6 or 7, blank padding occurs so that the value is a valid string representation of a time.

  TIMESTAMP

  • The length of the variable must not be less than 19. If the length is between 19 and 31, the timestamp is truncated like a string, causing the omission of one or more digits of fractional seconds. If the length is 20, the trailing decimal point is replaced by a blank so that the value is a valid string representation of a timestamp with a precision of 0.