MICROSECOND

The MICROSECOND function returns the microsecond part of a value.

Read syntax diagramSkip visual syntax diagramMICROSECOND (expression)
expression
An expression that returns a value of one of the following built-in data types: a date, a time, a timestamp, a character string, a graphic string, or a numeric data type.
  • If expression is a character or graphic string, its value must be a valid string representation of a datetime value. If expression is a valid string representation of a date, it must be in one of the IBM® SQL standard formats. For the valid formats of string representations of dates and timestamps, see String representations of datetime values.
  • If the argument is a DATE, it is first converted to a TIMESTAMP(0) value, assuming a time of exactly midnight (00.00.00).
  • If expression is a number, it must be a timestamp duration. For the valid formats of datetime durations, see Datetime operands and durations.

The result of the function is a large integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.

The other rules depend on the data type of the argument:

  • If the argument is date, time, or timestamp or a valid character-string representation of a date, time, or timestamp:

    The result is an integer that ranges from 0 to 999999.

    If the precision of the timestamp exceeds 6, the value is truncated.

  • If the argument is a duration:

    The result is the microsecond part of the value, which is an integer between -999999 and 999999. A nonzero result has the same sign as the argument.

Example

  • Assume a table TABLEA contains two columns, TS1 and TS2, of type TIMESTAMP. Select all rows in which the microseconds portion of TS1 is not zero and the seconds portion of TS1 and TS2 are identical. 
      SELECT *
    FROM TABLEA
    WHERE MICROSECOND(TS1) <> 0 AND SECOND(TS1) = SECOND(TS2)