DB2 10.5 for Linux, UNIX, and Windows

TIMESTAMP scalar function

The TIMESTAMP function returns a timestamp from a value or a pair of values.


>>-TIMESTAMP--(--expression1--+--------------+--)--------------><
                              '-,expression2-'

The schema is SYSIBM.

Only Unicode databases support an argument that is a graphic string representation of a date, a time, or a timestamp. In a Unicode database, if a supplied argument is a graphic string, it is first converted to a character string before the function is executed.

expression1 and expression2

The rules for the arguments depend on whether expression2 is specified and the data type of expression2.

If only one argument is specified it must be an expression that returns a value of one of the following built-in data types: a DATE, a TIMESTAMP, or a character string that is not a CLOB. If expression1 is a character string, it must be one of the following:
- A valid character string representation of a date or a timestamp. For the valid formats of string representations of date or timestamp values, see "String representations of datetime values" in "Datetime values".
- A character string with an actual length of 13 that is assumed to be a result from the GENERATE_UNIQUE function.
- A string of length 14 that is a string of digits that represents a valid date and time in the form yyyyxxddhhmmss, where yyyy is the year, xx is the month, dd is the day, hh is the hour, mm is the minute, and ss is the seconds.
If expression1 and expression2 are specified:
- If the data type of expression2 is not an integer:
  - expression1 must be a DATE or a valid string representation of a date and the expression2 must be a TIME or a valid string representation of a time.
- If the data type of expression2 is an integer:
  - expression1 must be a DATE, TIMESTAMP, or a valid string representation of a timestamp or date. expression2 must be an integer constant in the range 0 to 12 representing the timestamp precision.

The result of the function is a TIMESTAMP.

The timestamp precision and other rules depend on whether the second argument is specified:

If both arguments are specified and the second argument is not an integer:
- The result is a TIMESTAMP(6) with the date specified by the first argument and the time specified by the second argument. The fractional seconds part of the timestamp is zero.
If both arguments are specified and the second argument is an integer:
- The result is a TIMESTAMP with the precision specified in the second argument.
If only one argument is specified and it is a TIMESTAMP(p):
- The result is that TIMESTAMP(p).
If only one argument is specified and it is a DATE:
- The result is that date with an assumed time of midnight cast to TIMESTAMP(0).
If only one argument is specified and it is a string:
- The result is the TIMESTAMP(6) value represented by that string extended as described earlier with any missing time information. If the argument is a string of length 14, the TIMESTAMP has a fractional seconds part of zero.

If the arguments include only date information, the time information in the result value is all zeros. If either argument can be null, the result can be null; if either argument is null, the result is the null value.

Examples

Example 1: Assume the column START_DATE (whose data type is DATE) has a value equivalent to 1988-12-25, and the column START_TIME (whose data type is TIME) has a value equivalent to 17.12.30.
```
   TIMESTAMP(START_DATE, START_TIME)
```
Returns the value '1988-12-25-17.12.30.000000'.
Example 2: Convert a timestamp string with 7 digits of fractional seconds to a TIMESTAMP(9) value.
```
TIMESTAMP('2007-09-24-15.53.37.2162474',9)
```
Returns the value '2007-09-24-15.53.37.216247400'.