MICROSECOND scalar function

The MICROSECOND function returns the microsecond part of a value.

The schema is SYSIBM.

The argument must be an expression that returns a value of one of the following built-in data types: a timestamp, a character string, a graphic string, or a numeric data type.

If expression is a character or graphic string, it must not be a CLOB or DBCLOB, and its value must be a valid string representation of a timestamp with an actual length of not greater than 255 bytes. For the valid formats of string representations of times and timestamps, see String representations of datetime values.
If expression is a number, it must be a timestamp duration. For the valid formats of timestamp durations, see Datetime operands.

If expression is a timestamp with a time zone, or a valid string representation of a timestamp with a time zone, the result is determined from the UTC representation of the datetime value.

The result of the function is a large integer.

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 timestamp or string representation of a timestamp, the result is the microsecond part of the value, which is an integer in the range 0–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 1: Assume that table TABLEX contains a TIMESTAMP column named TSTMPCOL and a SMALLINT column named INTCOL. Select the microseconds part of the TSTMPCOL column of the rows where the INTCOL value is 1234:

   SELECT MICROSECOND(TSTMPCOL) FROM TABLEX
     WHERE INTCOL = 1234;

Example 2: The following invocations of the MICROSECOND function returns the same result:

SELECT MICROSECOND('2003-01-02-20.00.00.123456'), 
			MICROSECOND('2003-01-02-12.00.00.123456-08:00'), 
			MICROSECOND('2003-01-03-05.00.00.123456+09:00') 
		FROM SYSIBM.SYSDUMMY1;

For each invocation of the MICROSECOND function in this SELECT statement, the result is 123456.

When the input argument contains a time zone, the result is determined from the UTC representation of the input value. The string representations of a timestamp with a time zone in the SELECT statement all have the same UTC representation: 2003-01-02-20.00.00.123456.