Tip: Converting String to Number in QUERY/400 April 18, 2006
Theoretically there is no command to convert a string to number in Query/400, but I found this tip on an iSeries forum. It is little clumsy but can be handy workaround when you do not have access to SQL.
Here is what you do:
The TIMESTAMP function accepts a character representation of a valid time stamp. Because part of the time stamp is a 6 digit microsecond, use the character string to be converted for the microsecond.
* The date and time used is constant and is NOT important for the conversion
* The format of the microsecond is mmmmmm:
6 digits are required and they must be numbers.
If your field is less that 6 digits, it is to be Zero-filled to the left, for example, ‘0000xx’, for a 2-digit field.
If your field is more than 6 digits, do multiple conversions and use numeric operations to calculate the correct value.
If your field requires a decimal portion, do the conversion, then use numeric operations to convert to the correct decimal value.
Once the time stamp is created, the MICROSECOND function can be used to retrieve the numeric representation of the character field.
Following is an example:
fldString is a 4 digit character field in the file. Define the following result fields:
tStamp TIMESTAMP(’1995-01-01-12.00.00.00′||fldString)
fldNumeric MICROSECOND(TIMESTAMP)
If fldString has a value of ‘0045′, then tStamp will look like ‘1995-01-01-12.00.00.000045′ and fldNumeric will result in 45.
If you have a better solution, please post it here by clicking on comments.
Possible Related Links
