Datetime assignments

A string value that is assigned to a date, time, or timestamp column, variable, or parameter must be a valid string representation of a date, a time, or a timestamp.

A value that is assigned to a date column, a date variable, or a date parameter must be a date or a valid string representation of a date.

A date can be assigned only to the following items:

  • a date column
  • a character-string column
  • a character-string variable

A value that is assigned to a time column, a time variable, or a time parameter must be a time or a valid string representation of a time.

A time can be assigned only to the following items:

  • a time column
  • a character-string column
  • a character-string variable

Start of changeA value that is assigned to a timestamp column, a timestamp variable, or a timestamp parameter must be a timestamp, a timestamp constant, a valid string representation of a timestamp, or a valid string representation of a date. When a string representation of a date is assigned to a timestamp target, the DB2® database manager inserts zeroes for the time portion of the timestamp target. End of change

Start of changeA timestamp can be assigned only to the following items:End of change

Start of change
  • a timestamp column
  • a character-string or graphic-string column
  • a timestamp variable
  • a character-string or graphic-string variable
End of change

A valid string representation of a datetime value must not be a BLOB, CLOB, or DBCLOB. A datetime value cannot be assigned to a column that has a field procedure. Start of changeIf the timestamp precision of the target is less than the timestamp precision of the assigned value, the extra fractional seconds are truncated.End of change

When a datetime value is assigned to a character-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 fixed length character-string 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 the type of the target.

Start of changeWhen a datetime value is assigned to a timestamp variable or column, it is converted to the target timestamp data type. If the source data type is not the same as the target data type, the source value is implicitly cast to the target data type. DB2 might implicitly cast data types during assignments that involve a distinct type.End of change

  • Start of changeIf the target is not a variable and has a character-string or graphic-string data type (except for BLOB, CLOB, or DBCLOB), truncation is not allowed. The length of the column must be at least the following values:
    • 10 for a DATE
    • 8 for a TIME
    • 19 for a TIMESTAMP WITHOUT TIME ZONE with a precision of 0, 20+p with precision of p
    • Sufficient to include the time zone (truncation is not allowed), for a TIMESTAMP WITH TIME ZONE
    End of change
  • When the target is a character-string or graphic-string variable, the following rules apply:
    • For a date: The length of the variable must not be less than 10.
    • For a 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, or JIS 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, the value is padded with blanks so that it is a valid string representation of a time.

    • Start of changeFor a timestamp: The length of the variable must not be less than 19. If the source is TIMESTAMP WITH TIME ZONE, the length of the variable must be sufficient to include the time zone, truncation is not allowed.
      • If the length is between 19 and 31, the timestamp is truncated like a string, which causes the omission of one or more digits of the fractional seconds part of a timestamp.
      • If the length is 20, the trailing decimal point is excluded so that the value is a valid string representation of a timestamp with precision 0.
      End of change