IBM ISERIES SC41-5210-04 - Manual (Page 86)

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:
v If 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.
v If 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.
v If 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)
74 Query for iSeries Use V5R2