IBM SC41-5210-04 Year Query for iSeries function, Converting date formats in Query for iSeries

RESULT(x) = TIMESTAMP(DATEFRNK,TIMEFRNK)

YEAR Query for iSeries function

The YEAR function returns a numeric representation of the year part of a value. The form is:

YEAR ( expression )

The argument must be either a:

vDate

vTimestamp

vDate duration

vTimestamp duration

The result of the function is a binary field. If the argument can be null, the result can be null. If the argument is null, the result is null.

If the argument is a date or a timestamp, the result is the year part of the value, which is a binary field with a value between 1 and 9999.

If the argument is a date duration or a timestamp duration, the result is the year part of the value, which is a binary field with a value between −9999 and 9999. A nonzero result has the same sign as the argument.

Example:

YEAR(TIMESTAMP) where TIMESTAMP = 1991-10-22-12.15.23.123456

The resulting value of YEAR is the numeric value 1,991.

Additional date, time, and timestamp functions in Query for iSeries

Based on the system clock, the CURRENT function used with DATE, TIME, TIMESTAMP, or TIMEZONE returns the current system value corresponding to the function used. The TIMEZONE function is used to allow an easy conversion to Greenwich Mean Time (GMT) by subtracting CURRENT(TIMEZONE) from a local time value.

Note: If these functions are used more than once within a Query definition, all values are based on a single clock reading.

If your job date format is different than the format used in the query, Query for iSeries uses the job date format. If you use CURRENT(DATE) as a break field, you may get unexpected results when the job and query date format are different.

Example:

CURDAT = CURRENT(DATE)

CURTSP = CURRENT(TIMESTAMP)

Converting date formats in Query for iSeries

You may sometimes encounter circumstances in which you want to generate a report with the date in a format other than the one defined when the file was created.

Converting date for output to a database file in Query for iSeries

To convert a date field to a format different from the input file, you must externally define the output file with DDS specifying the date format for the output date field. This conversion occurs automatically.

80Query for iSeries Use V5R2