IBM SC41-5210-04 manual Date, time, or timestamp comparisons in Query for iSeries

Date, time, or timestamp comparisons in Query for iSeries

A date, time, or timestamp value may be compared either with another value of the same data type or with a character representation of that data type. All comparisons are chronological. The farther a point in time is from January 1, 0001, the greater the value of that point in time.

Comparisons of time values and character representations of time values always include seconds. If the character representation omits seconds, zero seconds are assumed. A time or timestamp value that includes 24:00:00 is not the same as 00:00:00.

Testing for equal (EQ) and not equal (NE) in Query for iSeries

You use the equal and not equal tests to determine if the contents of a field is equal or not equal to the value you specify.

If the test is EQ, records are selected only if the field contains data that is exactly the same as the specified value. For example, if the only comparison is:

INTRAT EQ 18

records are selected only if INTRAT, a numeric field, contains a value equal to 18. For example, INTRAT could contain 18., 18.00, 00018, and so on.

If the test is NE, records are selected only if the field contains data that is different than the specified value. For example, if you want to select records that contain anything other than a value of SMITH in the field NAME, you specify:

NAME NE ’SMITH’

It does not matter to Query if the field called NAME is defined to be longer than five characters. Query looks for all records that do not exactly match SMITH. SMITHSON, Smith, and NEISMITH would all be selected as names that are not equal to SMITH.

Testing for IS Null (IS) and ISNOT Null (ISNOT) in Query for iSeries

You use the IS and ISNOT tests to determine if the contents of any field is or is not null. Any field can be compared to null using the IS or ISNOT test. Some examples of IS and ISNOT are:

NAME IS NULL

Records are selected if the field NAME contains a null value.

NAME ISNOT NULL

Records are selected if the field NAME does not contain a null value.

Testing for greater (GT or GE), less (LT or LE), and range (RANGE) in Query for iSeries

You use the greater and less tests to determine if the contents of a field is greater than, greater than or equal to, less than, or less than or equal to the specified value. You use the range test to determine if the contents of a field lies within the specified range of values. In addition to using these to test numeric data, you can also test character data.

Some examples of greater and less tests are:

vNAME GT ’SMITH’

Records are selected if the field NAME contains a value in the collating sequence greater than SMITH.

vINTRAT GE 18

Records are selected if the field INTRAT contains a value that is greater than or equal to 18.

vBALDUE LT CRLIMIT

Records are selected if the data in the field BALDUE is less than the data in the field CRLIMIT.

vBALDUE LE CRLIMIT

96Query for iSeries Use V5R2