Query/400 does not provide a native method for converting character data to numeric, but with the following hack, the conversion is possible. I have a character field (4 digits) that’s used for generic purposes in a particular database file. Although the field’s format is character, the field sometimes represents numeric data. To perform math operations on such data in Query, you must first convert the field to numeric. By using the MICROSECOND keyword in Define Result Fields section of Query, you can convert the character data to a numeric field for later use in calculation.
For example, create a field CHARFIELD4 in this example represents a four-digit character field containing the character 0 through 9.
Next, use the MICROSECOND keyword on the TIMESTAMP field to create a numeric copy of CHARFIELD4’s character date. The MICROSECOND keyword takes the six rightmost digits in the TIMESTAMP field and converts them to numeric. To the example, the character field is only four digits long, so 00 is concatenated with the character field to create a valid timestamp with six digits of millisecond.
If a given character field is larger than six digits, you’ll need to break down the above process into multiple steps and perform a math operation on the individual numeric component to combine them.
Field Expression Column Heading Len Dec
TIMESTAMP ‘1988-12-25-17.30.00.00’ | | CHARFIELD 26
NUMERIC6 MICROSECOND(TIMESTAMP) 6 0