MICROSECOND scalar function

The MICROSECOND function returns the microsecond part of a value.

Read syntax diagramSkip visual syntax diagramMICROSECOND(expression )

The schema is SYSIBM.

expression
An expression that returns a value of one of the following built-in data types: DATE, TIMESTAMP, timestamp duration, or a valid character string representation of a date or timestamp that is not a CLOB. If a supplied argument is a DATE, it is first converted to a TIMESTAMP(0) value, assuming a time of exactly midnight (00.00.00). In a Unicode database, if a supplied argument is a GRAPHIC or VARGRAPHIC data type, it is first converted to a character string before the function is executed.

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 a DATE, TIMESTAMP, or a valid string representation of a date or timestamp:
    • The integer ranges from 0 through 999 999.
    • If the precision of the timestamp exceeds 6, the value is truncated.
  • If the argument is a duration:
    • The result reflects the microsecond part of the value which is an integer between -999 999 through 999 999. 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)