Time conversion—%TOTIME

Use this function when you want CDC Replication to convert a character, numeric, or time data type to a time data type during replication.

You can convert from different formats based on the type of the input value.
Use this function to when you want CDC Replication to track the date and time when it inserts or updates a row in source and target columns. This function uses the system clock on the source or target.
Note: The CDC Replication Engine for Db2® for i does not support this function.

Syntax

%TOTIME(time)

Parameters

time
Specifies a column or literal that can have one of the following types:
  • Time, in the format HMMSS or HHMMSS. For example, 71500 represents 7:15 AM and 223000 represents 10:30 PM.
  • Numeric. This value must be positive, and if it contains a fractional part, the fraction must be zero.
  • Character, in one of the following formats:
    [whitespace] digit digit digit digit digit digit [whitespace]
    This format lets you specify a time value that contains six consecutive digits. Any number of blank characters can precede or follow the six digits. For example, %TOTIME ( 012537 ) returns 01:25:37. 
    [whitespace] digit [digit] separator digit [digit] [separator digit [digit]] 
[whitespace]
    This format lets you specify a time value that contains valid separator characters (colon, comma, period or one or more spaces) between hours, minutes, and seconds. Any number of blank characters can precede the first digit or follow the last digit.

    You cannot specify more than one separator character in the time value. For example, %TOTIME (12:05.20) is not valid. You can omit the number of seconds, in which case the %TOTIME function assumes zero seconds. For example, %TOTIME (12:05) returns 12:05:00. You can omit leading zeros in the number of hours, minutes, and seconds. For example, %TOTIME (12 5 20) returns 12:05:20. 

    [whitespace] digit [digit] separator digit [digit] [whitespace] {A | a | P | 
p} [{M | m}] [whitespace]
    This format lets you specify a time value that indicates AM or PM. You can specify AM and PM in a number of different ways, such as A, AM, Am, a, aM, am, P, or PM. Any number of blank characters can precede the first digit or follow the AM/PM specification.

    You cannot specify more than one separator character in the time value. For example, %TOTIME (04: 20 PM) is not valid. You cannot specify seconds in this format. If you do so, the %TOTIME function assumes zero seconds. You can omit leading zeros in the number of hours and minutes. For example, %TOTIME (3 5 P) returns 15:05:00.

Note the following with respect to return values for this function:
  • If your input value is NULL, this function returns NULL.
  • If your input value is a Time this function returns a Time.
  • If CDC Replication encounters an error when parsing the input value, this function returns 00:00:00.

Result data type

This function returns a Time.

Examples

The following examples show the colon (:) as the separator character in the returned ISO (International Organization for Standardization) time values. Depending on your environment, a different character may separate the hours, minutes, and seconds in the output time.

Input value (time) Result
012537 01:25:37
22:4:12 22:04:12
4:05P 16:05:00
2 5 Am 02:05:00
204521 20:45:21
91035.0000 09:10:35
250521 00:00:00
10: 10:35 00:00:00