Query/400 provides many options for converting data, particularly dates, but lacks one important feature – converting character to numeric. However, there is a workaround even if it is a little “awkward” by using the MICROSECONDS function.

Firstly, append your character field to the end of an arbitrary date. Next, use the MICROSECONDS function to extract it as a numeric value.

Example:

                Define Result Fields

Field       Expression     
MYCHAR      '123456' 

TEMP        '2000-01-01-00.00.00.'!!mychar 
                                             
MYNUMERIC   microsecond(temp)    

This is a little limited because it will only work for 6 digit fields. To cater for longer numbers, we would need to split the field and reassemble after conversion. Here’s an example for a 10 digit date.

                Define Result Fields

Field       Expression                   Len  Dec
MYCHAR      '1234567890' 

TEMP1       '2000-01-01-00.00.00.'!! 
            substr(mychar,1,6)

TEMP2       '2000-01-01-00.00.00.'!!
            substr(mychar,7,4)       
                           
MYNUMERIC   microsecond(temp1)*10000+      10   0
            microsecond(temp2)/100     

Not pretty, but possible!