IBM SC41-5210-04 manual Date, time, and timestamp functions in Query for iSeries

Timestamp duration in Query for iSeries

A timestamp duration represents a number of years, months, days, hours, minutes, seconds, and microseconds expressed as a DECIMAL (20, 6) number. To be properly interpreted, the number must have the format yyyymmddhhMMsszzzzzz , where yyyy , mm , dd , hh, MM , ss and zzzzzz represent, respectively, the number of years, months, days, hours, minutes, seconds and microseconds. The result of subtracting one timestamp value from another is a timestamp duration.

Date, time, and timestamp functions in Query for iSeries

The following functions can be performed on date, time, and timestamp values:

CHAR Query for iSeries function

The CHAR function returns a character representation of a date, time, or timestamp value. An optional second argument dictates the SAA format for the result string. The first argument must be a date, time, or timestamp. The second argument, if used, is the name of a character format. The form is:

CHAR ( expression

,ISO

,USA

,EUR

,JIS

)

The result of the function is a fixed-length character constant. If the first argument can be null, the result can be null. If the first argument is null, the result is null. Other rules depend on the data type of the first argument as follows:

vIf the first argument is a date:

–A format should be specified, especially if:

-The query is to be used by different users.

-The query is to be sent to a different machine.

-A date has a two-digit year format attribute and the values may not be in the range of 1940 through 2039. Use this function to see the date in a four-digit year SAA format.

–If the second argument is omitted, the string format is the job format.

–The result is the character string representation of the date in the format specified by the second argument.

–The result length is 10 if a format is specified, 8 if no format is specified.

vIf the first argument is a time:

–If the second argument is omitted, the string format is the job format.

–The result is the character string representation of a time in the format specified by the second argument.

–The result length is 8.

vIf the first argument is a timestamp:

–The second argument is not applicable and must not be specified.

–The result is the character string representation of a timestamp.

–The result length is 26.

Example:

CHAR(HIREDATE,USA)

74Query for iSeries Use V5R2